Sum Distinct Count 1

[mtownbound]

Okay, I need to sum the distinct sales ID count at the dept, city, and state levels so here's my sample sales table:

[pre]Item ID Dept City State Region
Bicycle 001 Sports Atlanta GA East
Helmet 001 Sports Atlanta GA East
Watch 002 Home Atlanta GA East
Shoes 003 Home Atlanta GA East
Pants 003 Home Atlanta GA East
Shirt 003 Home Atlanta GA East
Mower 004 Lawn Atlanta GA East
Skates 005 Sports Atlanta GA East
Plates 001 Home Decatur GA East
[/pre]

And this is how I need to report:

[pre]State Sales
GA
Atlanta
Sports 2
Home 2
Lawn 1
Atlanta Total Sales 5
Decatur
Home 1
Decatur Total Sales 1
GA Total Sales 6

[/pre]
Using the "ControlSourece=1, Running Sum........" method to get the distinct count of the sales ID's at the dept level, but can't get the sum right at the city and state levels. I get 1 if I go Running sum=None.

Thanks!!

 

[rubbernilly]

If you are doing this in a report, you can do a GROUP BY on your unique-identifiers, then a COUNT on the items. Then, in your report, turn on Grouping and put your Summation fields in the section footers.

One caveat is that I don't see a unique sale identifier in your sample table. Your ID field is duplicated across Items, so I'm not sure what that represents. I'm going to assume you have or can produce a field that represents a unique sale... either a SaleID or a rendered SaleCount: 1 field in a query. For the example below, I assume the field is called [SaleID]...

SELECT [tblYourTable].State, [tblYourTable].City, [tblYourTable].Dept, COUNT([tblYourTable].[SaleID]) as CountOfSaleID
FROM [tblYourTable]
GROUP BY [tblYourTable].State, [tblYourTable].City, [tblYourTable].Dept
ORDER BY [tblYourTable].State, [tblYourTable].City, [tblYourTable].Dept;

Use that as the source for your report, and turn on Grouping. Give yourself groups for State and City, and turn on their Headers and Footers. In each section's footer, assign a textbox to have a Control Source of "=SUM([CountOfSaleID])

You should get what you're looking for.

 

[mtownbound]

Happy New Year!!

Thanks for the feedback. You're right, my example wasn't too clear. I tried your method but it returns the total ID group count versus distinct count. For example, I got a total of 3 for Sports instead of the distinct total of 2 (based on SalesId).

 

[SkipVought]

SELECT STATE, CITY, DEPT, COUNT(SALESID) AS CNT
FROM (
SELECT DISTINCT STATE, CITY, DEPT, SALESID
FROM TABLE)
GROUP BY STATE, CITY, DEPT

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mtownbound]

Worked like a champ!! Thanks!!

 

[mtownbound]

Okay, I was good with the distinct query but now we have to add total items along with the distinct count in the same report so it looks like this:

[pre]PK Item SalesID Dept City State Region
001 Bicycle 001 Sports Atlanta GA East
002 Helmet 001 Sports Atlanta GA East
003 Watch 002 Home Atlanta GA East
004 Shoes 003 Home Atlanta GA East
005 Pants 003 Home Atlanta GA East
006 Shirt 003 Home Atlanta GA East
007 Mower 004 Lawn Atlanta GA East
008 Skates 005 Sports Atlanta GA East
009 Plates 001 Home Decatur GA East
[/pre]

[pre]State Sales ID's Items Sold
GA
Atlanta
Sports 2 3
Home 2 4
Lawn 1 1
Atlanta Total Sales 5 Total Items 8
Decatur
Home 1 1
Decatur Total Sales 1 Total Items 1
GA Total Sales 6 GA Total Items 9
[/pre]

 

[rubbernilly]

If I'm understanding your data structure (based on you saying that proposed query working), then you should be able to add the ITEMSSOLD field to the DISTINCT statement, then SUM that on the outside:

SELECT STATE, CITY, DEPT, COUNT(SALESID) AS CNT, SUM(ITEMSSOLD) AS SUMITEMS
FROM (
SELECT DISTINCT STATE, CITY, DEPT, SALESID, ITEMSSOLD
FROM TABLE)
GROUP BY STATE, CITY, DEPT

 

[mtownbound]

I just added the subquery joined on ID and it worked. Thanks!!