Calculating max and joining tables

subquery

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

Best Answer

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

Related Solutions

MySQL: Optimize UNION with “ORDER BY” in inner queries

Just out of curiosity, can you try this version? It may trick the optimizer to use the same indices that the subqueries would use separately:

SELECT *
FROM
(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit,
 'hp' AS source FROM is_log AS l WHERE l.account_id = 730
 ORDER BY l.zeitpunkt DESC LIMIT 10) 
    AS a

UNION ALL

SELECT *
FROM
(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt,
 'ig' AS source FROM ig_is_log AS l WHERE l.account_id = 730
 ORDER BY l.zeitpunkt DESC LIMIT 10)
    AS b

ORDER BY zeit DESC LIMIT 10;

I still think that the best index you could have is the compound (account_id, zeitpunkt). It would yield the 10 rows fast, and no tricks would ne needed.

Mysql – Getting max values from MySQL tables

The answer to this depends on what date range you pick

EXAMPLE : Ranking Points Since The Beginning of the Month for Competition 'Kids Only'

SELECT * FROM
(
    SELECT
        A.name Athlete,R.ranking_points Rank,C.`date` CompDate
    FROM
    (
        SELECT id,`date` FROM competitions
        WHERE name = 'Kids Only' AND `date` >=
        DATE(NOW()) - INTERVAL (DAY(DATE(NOW()))-1) DAY + INTERVAL 0 SECOND;
    ) C
    INNER JOIN results R ON C.id = R.id_competition
    INNER JOIN athletes A ON R.id_athlete = A.id
) AA
ORDER BY Rank DESC;

EXAMPLE : Teenage Rankings For the Week of 2012-10-08 - 2012-10-15

SELECT * FROM
(
    SELECT
        A.name Athlete,R.ranking_points Rank,C.`date` CompDate
    FROM
    (
        SELECT id,`date` FROM competitions
        WHERE name = 'Teenage'
        AND `date` >= DATE('2012-10-08') + INTERVAL 0 SECOND
        AND `date` <  DATE('2012-10-15') + INTERVAL 0 SECOND
    ) C
    INNER JOIN results R ON C.id = R.id_competition
    INNER JOIN athletes A ON R.id_athlete = A.id
) AA
ORDER BY Rank DESC;

I hope these give you a basis to start...

Best Answer

Related Solutions

MySQL: Optimize UNION with “ORDER BY” in inner queries

Mysql – Getting max values from MySQL tables

Related Question