INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Sum Distinct Count

Sum Distinct Count

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

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
 

And this is how I need to report:

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
	
 
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!!

RE: Sum Distinct Count

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.

RE: Sum Distinct Count

(OP)
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).

RE: Sum Distinct Count

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Sum Distinct Count

(OP)
Worked like a champ!! Thanks!!

RE: Sum Distinct Count

(OP)
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:

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
 

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
 

RE: Sum Distinct Count

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:

CODE

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 

RE: Sum Distinct Count

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close