Jump to content

Archived

This topic is now archived and is closed to further replies.

hosefo81

Mysql queries problem

Recommended Posts

I want to find the customer with the highest sales for each category.

Because i have to make seven queries to get the customer with the highest sales for each category by using the method below.

Instead of making two or more queries by changing the where c.cat_id in each queries, can i get the result using only one queries?

If yes, can someone please tell me how by correcting my code?

 

select c.cat_id, s.cust_id, cu.cust_fname, cu.cust_lname, sum((sd.sales_price * sd.quantity)) as Total

from product p, sales_detail sd, sales s, category c, brand b, customer cu

where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and c.cat_id=b.brand_cat and p.prod_brand = b.brand_id and s.cust_id=cu.cust_id and year(s.sales_date) between \'1999\' and \'2003\' and c.cat_id=1

group by s.cust_id

order by Total desc

limit 1;

 

select c.cat_id, s.cust_id, cu.cust_fname, cu.cust_lname, sum((sd.sales_price * sd.quantity)) as Total

from product p, sales_detail sd, sales s, category c, brand b, customer cu

where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and c.cat_id=b.brand_cat and p.prod_brand = b.brand_id and s.cust_id=cu.cust_id and year(s.sales_date) between \'1999\' and \'2003\' and c.cat_id=2

group by s.cust_id

order by Total desc

limit 1;

Share this post


Link to post
Share on other sites

can\'t test it but maybe if you change

c.cat_id = 1

to

c.cat_id in (1,2,3,..)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.