Re: SQL for a statistic result set

Sorry for the error in my previous post - statement should be -

with cpcount as (select client, count(*) as productCount from cpfile group by client)
select productCount, count(*)
countOfProductClients from cpcount group by productCount order by productCount

This works and resulted in the following -

PRODUCTCOUNT COUNTOFPRODUCTCLIENTS
1 2
2 1
3 1

C1 has 2, C2 has 1, C3 has 1, C4 has 3

If you need all 7 rows - some would have 0 clients - they you'll need maybe a table with values 1 to 7 in 7 rows. Then maybe join that with the above statement, where the above statement is another table-expression

Basically you have cascading temporary result sets here.

HTH
Vern

On 9/2/2010 7:32 AM, David FOXWELL wrote:

Hi all,

I have a table like so :

Client Product
C1 P1
C1 P2
C2 P1
C3 P4
C4 P1
C4 P2
C4 P3

With the max, min and avg functions I can see that on average a client has 2 products, that no client has more than 7 products and none with less than 1.

But how can I output :

Number of products Number of clients
1 ?
2
3
4
5
6
7


Thanks.