calculating max and joining tables

Question

Just need a little help as new to SQL.

I have a table which contains readingtime (records every 4 seconds during the day) and an energy reading. I wanted to know how much energy was used in a day. this is the query that i used

SELECT date(readingtime), max(energy)-min(energy)as total 
FROM lights_1 
WHERE readingtime between 20140407000000 and 20140409235959 
GROUP BY date(readingtime) LIMIT 0,30;

This gave me the desired results for 1 table. I have 4 tables of these lights_2, Lights_3, lights_4 and i would like to display them together and hence match up the reading time so each day has the energy requirements for all for tables with the total. When i try and join tables i get a lot of errors!

Can anyone help.

thanks

Union gives

date(readingtime) total
2014-04-07        1244
2014-04-08        1039
2014-04-09        1389
2014-04-07        419
2014-04-08        1102
2014-04-09        482

I would like

date(readingtime) total_1  total_2
2014-04-07        1244      419
2014-04-08        1039      1039
2014-04-09        1389      1389

Answer 1

SELECT  date(lights_1.readingtime), 
        max(lights_1.energy)-min(lights_1.energy)as total_1, 
        max(lights_2.energy)-min(lights_2.energy)as total_2
FROM lights_1
join lights_2 on lights_1.readingtime = lights_2.readingtime
WHERE lights_1.readingtime between 20140407000000 and 20140409235959 
GROUP BY date(lights_1.readingtime) LIMIT 0,30

Answer 2

You first need to UNION the tables:

SELECT date(readingtime), max(energy)-min(energy)as total 
FROM lights_1 
WHERE readingtime between 20140407000000 and 20140409235959 
GROUP BY date(readingtime) LIMIT 0,30
UNION
SELECT date(readingtime), max(energy)-min(energy)as total 
FROM lights_2 
WHERE readingtime between 20140407000000 and 20140409235959 
GROUP BY date(readingtime) LIMIT 0,30
UNION
SELECT date(readingtime), max(energy)-min(energy)as total 
FROM lights_3 
WHERE readingtime between 20140407000000 and 20140409235959 
GROUP BY date(readingtime) LIMIT 0,30
SELECT date(readingtime), max(energy)-min(energy)as total 
FROM lights_4 
WHERE readingtime between 20140407000000 and 20140409235959 
GROUP BY date(readingtime) LIMIT 0,30;

You then will need to pivot the result of this query. I assume you are using MySQL? I could go through and figure out everything for you, but its probably better if you go and learn how to pivot the data to get it the way you want, the following is a good summary! http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/