mysql查询 - 一个查询来创建按月显示销售额的表

Question

I have a mysql table with sales information: Seller, product, sales date.

How do I write a query to generate a table that shows total products shown by each seller, per month, and in total?

This is the query I use to create a table with SellerName and total sales in July:

select SellerName, count(*) as july 
from onlineDATA
where ReportMonth = 201207
GROUP BY SellerName
ORDER BY july DESC
LIMIT 0,30

How do I modify this to get additional sales totals for each month?

I am looking for output like this:

    SellerName   | Jun  | Jul  | Aug  | YTD
    John Doe     | 30   |  25  |  30  | 85

Bonus question - how would I code this in PHP when the number of months would be a user input - anywhere from 1 to 12?

Thanks

Answer 1

I think you will have to define the columns separately, like this:

SELECT 

SellerName,     
SUM(IF(ReportMonth = 201206, 1.0)) AS Jun,
SUM(IF(ReportMonth = 201207, 1.0)) AS Jul,
SUM(IF(ReportMonth = 201208, 1.0)) AS Aug,
COUNT(*) AS YTD

FROM onlineDATA

GROUP BY SellerName 
ORDER BY SellerName DESC LIMIT 30;