Jump to content


Photo

Mysql queries problem


  • Please log in to reply
1 reply to this topic

#1 hosefo81

hosefo81
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 18 December 2003 - 11:55 AM

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;

#2 Tmic

Tmic
  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 18 December 2003 - 12:21 PM

can\'t test it but maybe if you change
c.cat_id = 1
to
c.cat_id in (1,2,3,..)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users