Selecting data from the same column multiple times with different conditions

Question

I have a table which is used to store total power consumption from 3 different floors. 3 new rows are added daily, with one record for each floor.

--------------------------------------------------
--FloorNumber--TotalDailyConsumption--DateStamp---
--------------------------------------------------
--     1     --        1200         --2018-03-10--
--     2     --        1650         --2018-03-10--
--     3     --        1900         --2018-03-10--
--     1     --        1400         --2018-03-09--
--     2     --        1150         --2018-03-09--
--     3     --        1270         --2018-03-09--
--------------------------------------------------

I'm trying to create a query that retuns the total daily consumption for each floor over the last 7 days. The data is automatically being inserted into the database.

----------------------------------------
--DateStamp---Floor1--Floor2--Floor3----
----------------------------------------
--2018-03-10-- 1200 -- 1650 --  1900  --
--2018-03-09-- 1400 -- 1150 --  1270  --
----------------------------------------

I currently have the following query, which I know only returns the values for floor 1, however I cannot figure out how to extend this to include the other two floors.

SELECT DateStamp, TotalDailyConsumption AS Floor1 FROM floor WHERE
FloorNumber = 1 AND DateStamp >= DATE_ADD(CURDATE(), INTERVAL -7 day)
ORDER BY DateStamp DESC

Answer 1

You want group by and conditional aggregation:

SELECT DateStamp,
       SUM(CASE WHEN FloorNumber = 1 THEN TotalDailyConsumption END) AS Floor1,
       SUM(CASE WHEN FloorNumber = 2 THEN TotalDailyConsumption END) AS Floor2,
       SUM(CASE WHEN FloorNumber = 3 THEN TotalDailyConsumption END) AS Floor3
FROM floor f
WHERE DateStamp >= DATE_ADD(CURDATE(), INTERVAL -7 day)
GROUP BY DateStamp
ORDER BY DateStamp DESC;

Answer 2

Although Gordon's answer serves your needs, but also if you want to show floor's weekly consumption:

SELECT FloorNumber, 
SUM(TotalDailyConsumption) as WeeklyConsumption 
FROM test
WHERE DateStamp >= DATE_ADD(CURDATE(), INTERVAL -7 day) 
GROUP BY FloorNumber 
ORDER BY FloorNumber ASC;