How to construct a MySQL query that uses a group by statement to SUM() values, and also includes extra columns that are used in the summed values?
I have a mysql database with one sales table.
CREATE TABLE `sales` (
`user` varchar(255) DEFAULT NULL,
`products_sold` int DEFAULT NULL,
`location` varchar(255) DEFAULT NULL,
`day` varchar(255) DEFAULT NULL
);
And the data looks something like…
user | products_sold | location | day
Bob | 10 | Mars | Tuesday
Randy | 12 | Venus | Thursday
Bob | 7 | Venus | Thursday
Note that the intention is for the database to maintain this exact same structure. So I will not be adding a new foreign key table for user or anything like that… it will stay simple and flat like above.
What I want to do is construct an SQL query that provides a result displaying:
- The users (Bob, Randy)
- The total number of products sold for the specific user
- The total number of products sold for the user on Tuesday
- The total number of products sold for the user on Thursday
So the result would look like:
user | total_sold | tuesday_total | thursday_total
Bob | 17 | 10 | 7
Randy | 12 | 0 | 12
I have a SELECT statement that retrieves the first two columns successfully:
select user, sum(products_sold) as total_sold from sales
group by user order by total_sold desc
This query retrieves the first two columns I am looking for, like so:
user | total_sold
Bob | 17
Randy | 12
So my question is, how do I construct a query that retrieves the second two columns as well?
Any help is much appreciated.
>Solution :
What you’re asking for is a Pivot.
In Mysql you can make use of a boolean expression within a sum, so adding to your existing query for your specific example:
select user,
sum(products_sold) total_sold ,
sum((day = 'Tuesday') * products_sold) tuesday_total,
sum((day = 'Thursday') * products_sold) thursday_total
from sales
group by user
order by total_sold desc;
Demo Fiddle