Very Computer

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

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

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

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

--
Tony Rogerson
Torver Computer Consultants Ltd.

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