Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Numbering Group Headers, then sum

Status
Not open for further replies.

msi30502

Technical User
May 9, 2012
30
US
Hi,

I'm having difficulty with the following:
---------------------------------------------------

SELECT COUNT (*)
FROM OE_Orders AS s

WHERE EXISTS
( SELECT *
FROM OE_Orders AS t
WHERE s.CustomerKey = t.CustomerKey
AND t.CustomerKey= 1
)

GROUP BY s.CustomerKey
---------------------------------------------------

As you can see in my attachment I'm only getting zero values. I need each customer code to represent 1 stop, regardless of the number of orders.

Please help.
 
 https://files.engineering.com/getfile.aspx?folder=69ea278e-ab03-4ff6-9688-8d3606a607a6&file=Document_Delivery_Count_by_Driver_on_2021-08-03_153627.pdf
What value as a Count do you get when you run your Select for one particular Cust Code?
[tt]
SELECT COUNT (*)
FROM OE_Orders AS s
...
GROUP BY s.CustomerKey[/tt] <- Why here is an Order By?

Above SELECT COUNT will give you just one outcome/ one number.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Thanks for the Reply @Andrzejek. To answer your questions/comments,

1. When I select a particular customer code I get zero.
2. "Why here is an Order By?" - I copied the code from another website and filled in the blanks. After your question, I removed the GROUP BY.
3. I don't mind getting just one outcome/number. As per my attachment, you can see that I'm trying to get each CustomerCode to count as 1, and then have all the 1's sum at the bottom of the column.

Another option would be to use a line count in the CustomerCode Header. My objective will be met as long as I get the total number of customers.
However, I don't the code to generate line numbers.
 
>My objective will be met as long as I get the total number of customers.

How about:
[tt]
Select COUNT(*) As NoOfCustomers
From tblCustomers
Where ...[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Is this what you mean?

SELECT COUNT (*)
FROM AR_Customers AS s

WHERE EXISTS
( SELECT *
FROM OE_Orders AS t
WHERE s.CustomerKey = t.CustomerKey
AND t.CustomerKey = 1
)

Because the result is still zero.
 
Your SQL does not make any sense to me.
It looks like you are testing if any records (well, just one record with one row) from:
[tt]
SELECT COUNT (*)
FROM AR_Customers
[/tt]
exist in
[tt]
SELECT *
FROM OE_Orders AS t
WHERE AR_Customers.CustomerKey = t.CustomerKey
AND t.CustomerKey = 1[/tt]

Unless the second SQL returns one column of numbers, which means [tt]OE_Orders[/tt] table has one numeric column of data and that's the only column in this table.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
It looks like you are attempting to report some data. Where are you using your SQL statement? Can you back out a bit and tell us what you are trying to accomplish with some table structures?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Okay, let me back up.

I have two tables AR_Customers and OE_Orders.
AR_Customers is just the list of customers with basic information (name, number, address, etc.)
OE_Orders holds information about each order that is processed (product, prices, item codes, etc.)
They are both linked by the .CustomerKey

In my report, I select 2 filters, OE_Orders.OrderDate and OE_Orders.RouteCode. From there, group the results by OE_Orders.CustomerKey and use the AR_Customer table simply to show me the customer name because I don't know the customers by their CustomerKey number.

In reality, the only useful data comes from the OE_Orders table.

If I just run the report with a sum of invoices by date and route, I will get a count of all invoices regardless of the number of customers. Sometimes customers have more than 1 invoice per day.
What I need is the number of Customers (whether they have 1 invoice or multiple). It doesn't matter if I get it by numbering lines by customer or if by generating a unique count sum of all the customers.

Hope this helps. Thanks again for everything.
 
>In my report, I select 2 filters, OE_Orders.[blue]OrderDate[/blue] and OE_Orders.[blue]RouteCode[/blue]
>In reality, the only useful data comes from the OE_Orders table
>What I need is the number of Customers

How about:
Code:
Select COUNT(DISTINCT CustomerKey)
From OE_Orders
Where OrderDate = [blue]'08/06/2021'[/blue]
And RouteCode = [blue]12[/blue]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Just tried it and didn't work:
It assigned each transaction/invoice a default value of 423, then summed by customer and by grand total (see attached)
Code:
Select COUNT(DISTINCT CustomerKey)
From OE_Orders
Where OrderDate = OE_Orders.InvoiceDate
And RouteCode = OE_Orders.RouteCode
 
 https://files.engineering.com/getfile.aspx?folder=72d5eb5e-208c-4c30-855a-29cb7dbd5329&file=Document_Delivery_Count_by_Driver_on_2021-08-06_114734.pdf
>it and didn't work
Did you get an error? "didn't work" is too vague..

Looks to me that your requirements keep changing. Nowhere InvoiceDate was mentioned before, RouteCode = OE_Orders.RouteCode equals itself...?

If your data looks something like this:
[pre]
OE_Orders table
CustomerKey OrderDate RouteCode ...[blue]
1 08/06/2021 12
1 08/06/2021 12[/blue]
1 08/06/2021 13[blue]
2 08/06/2021 12[/blue]
2 02/02/2021 12
2 03/03/2020 11
3 04/04/2000 12
3 05/05/2000 12
[/pre]
My SQL should return the count of 2 (blue records)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
My appologies I incorrectly typed OE_Orders.OrderDate where I should have written OE_Orders.InvoiceDate

In your code,

Where OrderDate = '08/06/2021'
And RouteCode = 12

you assigned hard values.

I need it to pull the values from my parameter settings. How can I do that?
 
In my book, I ALAWYS start with the hard-coded values just to be sure my SQL works correctly. Only then I replace these values with parameters/variables/user input, etc.

If you do use hard-coded values, do you get the correct outcome?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Almost there. The values are now 4, which IS the total number of stops. See attached
 
Is this a Microsoft Access report or other? Access allows you to create a running sum text box in a header which can count the number of unique customers.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Hi, this is a proprietary program that was developed by Aspen Systems. They included a report generator that allows me to creat reports using SQL expressions.
 
So, what is the final 'SQL expression' that works for you for the number of Customers?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top