Jump to content


Photo

How to select Max


  • Please log in to reply
No replies to this topic

#1 hosefo81

hosefo81
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 21 November 2003 - 05:06 AM

i successfully list out the customer that buy my product every month using mysql. But i can\'t list out the customer that have the highest sales for every month. please help. Below are my sql command.

select month(s.sales_date), s.cust_id, sum((p.prod_price * sd.quantity)) as Total
from product p, sales_detail sd, sales s, category c
where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and c.category_id=p.prod_cat and year(s.sales_date) = \'1999\'
group by month(s.sales_date), s.cust_id;

+---------------------+---------+-------+
| month(s.sales_date) | cust_id | Total |
+---------------------+---------+-------+
| 1 | C001 | 134 |
| 1 | C004 | 162 |
| 2 | C001 | 390 |
| 2 | C020 | 354 |
| 3 | C001 | 522 |
| 3 | C002 | 153 |
| 3 | C013 | 93 |
| 3 | C014 | 50 |
| 3 | C020 | 321 |
| 4 | C001 | 493 |
| 4 | C012 | 33 |
| 4 | C013 | 36 |
| 4 | C014 | 197 |
| 5 | C010 | 487 |
| 5 | C011 | 461 |
| 5 | C020 | 430 |
| 6 | C001 | 667 |
| 6 | C020 | 289 |
| 7 | C001 | 44 |
| 7 | C019 | 178 |
| 7 | C020 | 78 |
| 8 | C001 | 125 |
| 9 | C001 | 144 |
| 9 | C002 | 78 |
| 9 | C003 | 36 |
| 10 | C001 | 250 |
| 10 | C018 | 304 |
| 10 | C019 | 210 |
| 10 | C020 | 308 |
| 11 | C001 | 346 |
| 11 | C020 | 68 |
| 12 | C001 | 203 |
| 12 | C002 | 220 |
|+---------------------+---------+----

i try to code it like this but to no avail. please help
select month(s.sales_date), s.cust_id, Max(sum((p.prod_price * sd.quantity)) )as Total
from product p, sales_detail sd, sales s, category c
where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and c.category_id=p.prod_cat and year(s.sales_date) = \'1999\'
group by month(s.sales_date), s.cust_id;




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users