Re: SQL for a statistic result set

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.