Re: SQL for a statistic result set

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

 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.