Re: SQL for a statistic result set

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

 David

I'm going to start out being really stupid here, so I'm asking a couple 
things.

Are you asking this? How many clients have a certain count of products? 
If so, you need to get temporary results. One would be a list of clients 
and their product count, as here -

select client, count(*) from file group by client

Put that in a select as table-expression, either using WITH AS or nested -

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

That's a WAG - haven't actually tried it.

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.