Query to find top [salesperson] per month

[wrightee]

Hi:

Can this be done with a single query on MySQL 4.1, or do I need to run multiple queries and process server side:

Table looks something like this:

[tbl_sales]
- lead_id
- dated
- salesperson
- sold_amount

I want to get the following back:

APR06 - Fred - £500
MAR06 - Mary - £1200
FEB06 - Jon - £700

..i.e. for each month, show me which salesperson sold the most and how much that was.

Thanks in advance for your time..

[txbakers]

select max(sold_amount), salesperson from table group by dated order by dated

should work.

[wrightee]

Sorry; my question was poorly stated. The total sales figures need to be sum'd, i.e. table data is:

1 - fred - £50 - 01-apr-06
2 - fred - £75 - 02-apr-06
3 - mary - £20 - 04-apr-06
4 - john - £60 - 01-may-06

..so result should show:

APR06 - £125 - fred
MAY06 - £60 - john

[lovethecoast]

SELECT salesperson, SUM(sold_amount), DATEPART(m, dated), DATEPART(yyyy, dated)
FROM tbl_sales
GROUP BY salesperson, DATEPART(m, dated), DATEPART(yyy, dated)
ORDER BY DATEPART(yyyy, dated) DESC, DATEPART(m, dated) DESC, SUM(sold_amount) DESC

S