Query of counts

I have a table with three fields: AcctNo INT, Code CHAR(1), Amount MONEY

The Code has three values: 'A', 'B', or 'C'.

Each AcctNo has 1, 2, or all 3 of the Codes assigned to it (ie. AcctNo is not unique)

I need to know how many AcctNos have one value assigned to the Code field, how many have two, and how many have three and I need to know the sum of the Amount for each group.

Can this be done in one statement or do I need three statements?

Fred

The query below returns the results as requested. Is this what you're looking for?

Chris

DECLARE @.Values TABLE (AcctNo INT, Code CHAR(1), Amount MONEY)

INSERT INTO @.Values(AcctNo, Code, Amount)

SELECT 1, 'A', 1.00 UNION

SELECT 1, 'B', 2.50 UNION

SELECT 2, 'C', 1.25 UNION

SELECT 3, 'C', 1.43 UNION

SELECT 3, 'A', 1.96 UNION

SELECT 3, 'B', 2.00 UNION

SELECT 4, 'C', 1.43 UNION

SELECT 4, 'A', 1.96 UNION

SELECT 4, 'B', 2.10 UNION

SELECT 5, 'B', 0.92 UNION

SELECT 5, 'A', 1.24 UNION

SELECT 6, 'C', 0.02 UNION

SELECT 7, 'B', 0.11

SELECT SUM([AccountNoCount]), SUM(TotalAmount), [CodeCount]

FROM (SELECT COUNT(DISTINCT AcctNo) AS [AccountNoCount], SUM(Amount) AS TotalAmount, COUNT(Code) AS [CodeCount]

FROM @.Values

GROUP BY AcctNo) t

GROUP BY [CodeCount]

|||

Yes, that is what I wanted first.

But I need to add another level of complexity. There are duplicates of the AccountNo and Code, only the Amount is different. I need to count duplicates as one. (or anything more than one; there were some with three times and four)

Thanks,

Fred

|||

All you should need is an extra DISTINCT, see below.

Chris

SELECT SUM([AccountNoCount]), SUM(TotalAmount), [CodeCount]

FROM (SELECT COUNT(DISTINCT AcctNo) AS [AccountNoCount], SUM(Amount) AS TotalAmount, COUNT(DISTINCT Code) AS [CodeCount]

FROM @.Values

GROUP BY AcctNo) t

GROUP BY [CodeCount]