T-SQL ? Max(count ????) - Help

T-SQL ? Max(count ????) - Help

Hi all,

I have a SQL query question which I am trying  (so far in vein) to figure
out.
What I need to do is:
1. Determine the different products a certain customer has
2. Count how many of each product
3. Then determine which is the pre* product (i.e. has the highest
count for this product.)

there are only 8 products available.

Sample data:

Cust_no        Prod_type

12345            A
12345            B
12345
12345            B
12354            B
12354            B
12354            C

I want to do this as a batch on the server.  I think I can do it from a
client app, but I would involve 8 select count queries for each customer
(were talking millions of records.......)

If there is a way to do this, I would really appreciate some pointers.

TIA

Kurt

T-SQL ? Max(count ????) - Help

You can do it in a fairly straight-forward way using a view and a stored
procedure:

CREATE VIEW Cust_Prod_HowMany
AS
select distinct
a.Cust_no,
a.Prod_type,
HowMany = (select count(*) from Cust_Prod b
where b.Cust_No = a.Cust_no and
b.Prod_type = a.Prod_type)
from Cust_Prod a

CREATE PROCEDURE *ProductsGet
AS
select Cust_no, *Product = Prod_type
from Cust_Prod_HowMany a
where HowMany = (select max(HowMany) from Cust_Prod_HowMany b
where b.Cust_no = a.cust_no)

Others may be able to offer more elegant or more efficient solutions, but
this seems to get the job done.

Dave Plummer

>Hi all,

>I have a SQL query question which I am trying  (so far in vein) to figure
>out.
>What I need to do is:
>1. Determine the different products a certain customer has
>2. Count how many of each product
>3. Then determine which is the pre* product (i.e. has the highest
>count for this product.)

>there are only 8 products available.

>Sample data:

>Cust_no        Prod_type

>12345            A
>12345            B
>12345
>12345            B
>12354            B
>12354            B
>12354            C

>I want to do this as a batch on the server.  I think I can do it from a
>client app, but I would involve 8 select count queries for each customer
>(were talking millions of records.......)

>If there is a way to do this, I would really appreciate some pointers.

>TIA

>Kurt

T-SQL ? Max(count ????) - Help

Quote:>1. Determine the different products a certain customer has
>2. Count how many of each product

select Cust_no, Prod_type, count(*)
from ....
group by Cust_no, Prod_type

Quote:>3. Then determine which is the pre* product (i.e. has the highest
>count for this product.)

-- in order, most first, least last
select Prod_type, cnt = count(*)
from ....
group by Prod_type
order by cnt desc

--
Tony Rogerson
Torver Computer Consultants Ltd.

>Hi all,

>I have a SQL query question which I am trying  (so far in vein) to figure
>out.
>What I need to do is:
>1. Determine the different products a certain customer has
>2. Count how many of each product
>3. Then determine which is the pre* product (i.e. has the highest
>count for this product.)

>there are only 8 products available.

>Sample data:

>Cust_no        Prod_type

>12345            A
>12345            B
>12345
>12345            B
>12354            B
>12354            B
>12354            C

>I want to do this as a batch on the server.  I think I can do it from a
>client app, but I would involve 8 select count queries for each customer
>(were talking millions of records.......)

>If there is a way to do this, I would really appreciate some pointers.

>TIA

>Kurt

T-SQL ? Max(count ????) - Help

U should execute the following sql query for the desired result

select cust_no, prod_type, count(*) from table group by
cust_no, prod_type, count(*) order by cust_no_product_type

Prem Sapra

Quote:> Hi all,

> I have a SQL query question which I am trying  (so far in vein) to figure
> out.
> What I need to do is:
> 1. Determine the different products a certain customer has
> 2. Count how many of each product
> 3. Then determine which is the pre* product (i.e. has the highest
> count for this product.)

> there are only 8 products available.

> Sample data:

> Cust_no        Prod_type

> 12345            A
> 12345            B
> 12345
> 12345            B
> 12354            B
> 12354            B
> 12354            C

> I want to do this as a batch on the server.  I think I can do it from a
> client app, but I would involve 8 select count queries for each customer
> (were talking millions of records.......)

> If there is a way to do this, I would really appreciate some pointers.

> TIA

> Kurt

Hi all,

I have a SQL query question which I am trying  (so far in vein) to figure
out.
What I need to do is:
1. Determine the different products a certain customer has
2. Count how many of each product
3. Then determine which is the predominant product (i.e. has the highest
count for this product.)

there are only 8 products available.

Sample data:

Cust_no        Prod_type

12345            A
12345            B
12345
12345            B
12354            B
12354            B
12354            C

I want to do this as a batch on the server.  I think I can do it from a
client app, but I would involve 8 select count queries for each customer
(were talking millions of records.......)

If there is a way to do this, I would really appreciate some pointers.

TIA

Kurt