How to select Max

[hosefo81]

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;