CODEWITHSUNDEEP
phpmysqlsql
Milosz
Milosz

Reputation: 437

MySql complex query to count each person orders

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.

Upvotes: 0

Views: 135

Answers (2)

SylvainL
SylvainL

Reputation: 3938

If the person's name is just a string, then what you need is to Group By on a Case expression that will return 1 for the first person, 2 for the second, etc.; something like:

Group by Case When orders.comment LIKE '%Adam%' then 1 When ... End, ...

Or course, the real solution would be to add a table for Persons and add a relationship to it.

Upvotes: 1

radar
radar

Reputation: 13425

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

Upvotes: 1

Related Questions