Jump to content

Mysql queries problem


hosefo81

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;

Link to comment
https://forums.phpfreaks.com/topic/1523-mysql-queries-problem/
Share on other sites

Archived

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

×
×
  • Create New...

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.