MySql复杂查询来统计每个人的订单

Question

I have a table with orders in which in Comments record there is a name of the order creator. There is 3 sales people. I want to fetch statistics from each sales person. I came up with below query to output one person's orders and it works fine but I really struggle if it possible in one select query to fetch orders of each sales person and output in the same table. I tried Union and select within select but I guess I am constructing the query wrong. The below works fine to output just Adam's orders(Qty sold and total Sales value for that person).Thanks for any tips.

SELECT MONTHNAME(orders.despatched) as Month, YEAR(orders.despatched) as Year, 
SUM(orders.price) as AdamSales, COUNT(orders.comment) as AdamQt FROM orders 
WHERE 
orders.comment LIKE '%Adam%' AND 
orders.status = 'despatched' AND 
orders.despatched BETWEEN '$d1' AND '$d2' 
GROUP BY YEAR(orders.despatched), MONTH(orders.despatched)
order by orders.despatched ";

I know that possibly grouping by Person would be best if only the person's name wasn't just a string somewhere inside Comment record.

Answer 1

you can do group by on the case statement based on the comments

SELECT 
       ( case when ORDERs.comments LIKE '%Adam%' THEN 'Adam'  
                 when ORDERS.comments LIKE '%Peter%' THEN 'Peter' END ) as 'Person'
       MONTHNAME(orders.despatched) as Month, 
       YEAR(orders.despatched) as Year, 
       SUM(orders.price) as Sales, 
       COUNT(orders.comment) as Qt FROM orders 
WHERE 
(orders.comment LIKE '%Adam%' OR orders.comment LIKE '%Peter%' ) AND
orders.status = 'despatched' AND 
orders.despatched BETWEEN '$d1' AND '$d2' 
GROUP BY YEAR(orders.despatched), MONTH(orders.despatched),
     ( case when ORDERs.comments LIKE '%Adam%' THEN 'Adam'  
                 when ORDERS.comments LIKE '%Peter%' THEN 'Peter' END )
order by orders.despatched