Groups and the COUNT Function

[Former Member]

Hello,

I am wanting to insert a count formula that searches throughout an entire report, but is contained within a group. To explain, here is an example data set:

SalesmanID	ClientName
1	A
2	A
3	C
1	B
2	C
1	C

My report is needing to be grouped by ClientName and should look like this:

----------------------------------------------

Client A

     Salesman 1     F=3

     Salesman 2     F=2

Client B

     Salesman 1     F=3

Client C

     Salesman 1     F=3

     Salesman 2     F=2

     Salesman 3     F=1

----------------------------------------------

Where F is equal to the amount of times "Salesman X" appears in the entire report. Using the COUNT and Running Total methods, I have only been able to count through the number of instances in each Client group, not through the entire report. Since I am dealing with a large number of salesman IDs, F must be dynamic. How should I go about writing this formula?

Thank you!

[nscheaffer · Accepted solution]

There are most likely many ways to accomplish this.

First I created the table as you had laid out...

CREATE TABLE SalesmanClient (
    SalesmanID INT,
    ClientName VARCHAR(25))

INSERT INTO SalesmanClient VALUES (1, 'A')
INSERT INTO SalesmanClient VALUES (2, 'A')
INSERT INTO SalesmanClient VALUES (3, 'C')
INSERT INTO SalesmanClient VALUES (1, 'B')
INSERT INTO SalesmanClient VALUES (2, 'C')
INSERT INTO SalesmanClient VALUES (1, 'C')

My approach was to get the the count of the SalesmanIDs from SalesmanClient regardless of the client (Totals).  I then joined back to the SalesmanClient (Details) to get the client...

SELECT 
Details.ClientName, 
Totals.SalesmanID, 
Totals.Count
FROM
(
    SELECT SalesmanID, COUNT(SalesmanID) AS Count
    FROM SalesmanClient
    GROUP BY SalesmanID
) Totals
INNER JOIN SalesmanClient Details 
     ON Totals.SalesmanID = Details.SalesmanID
ORDER BY 1, 2

Here is a link (not sure how long that will work) it is in action on SQL Fiddle...

If you put that SELECT statement in a Command Object you should be well on your way.

Noel