Mysql Question posted in
The official MySQL documentation can be found here.

MySQL query with 2 different where statements on same column

I am trying to write a MySQL query for graphing purposes, the graph is for a single column but at different times of the day.

I have a table which is measuring the State of Charge of a large battery and I want to produce a table for graphing where I can get the SoC at roughly 2am and 7am

This is one SELECT that I am using for the 2am

SELECT luxpower.`Date_MySQL`,
       luxpower.`soc`
FROM luxpower 
WHERE luxpower.`Minutes_Since_Midnight` <= 120 
GROUP BY luxpower.`Date_MySQL`

and this is the SELECT I am suinf for 7am

SELECT luxpower.`Date_MySQL`,
       luxpower.`soc`
FROM luxpower 
WHERE luxpower.`Minutes_Since_Midnight` <= 420 
GROUP BY luxpower.`Date_MySQL`

What I would like is the combine into a single query with soc as 2 different alias, something like this (which does not work)

SELECT luxpower.`Date_MySQL`,
       luxpower.`soc` as socama (FROM luxpower WHERE luxpower.`Minutes_Since_Midnight` <= 120 GROUP BY luxpower.`Date_MySQL`)
AND 
SELECT luxpower.`Date_MySQL`,
       luxpower.`soc` as socamb (FROM luxpower WHERE luxpower.`Minutes_Since_Midnight` <= 420 GROUP BY luxpower.`Date_MySQL`)

I am a recordset to be like

Date_MySQL,socama ,socamab
2024-06-01,10,7
2024-06-02,15,17
2024-06-03,23,44
2024-06-04,10,32

Thanks

Tags:

2

Answers


  1. 0

    You can achieve this by having two sub queries then join them.

    SELECT query1.`Date_MySQL`,
   query1.`soc` as socama,
   query2.`soc` as socamb FROM (
SELECT `Date_MySQL`,
       MAX(`soc`) as soc
FROM luxpower
WHERE `Minutes_Since_Midnight` <= 120 
GROUP BY `Date_MySQL`) query1 JOIN (
SELECT `Date_MySQL`, MAX(`soc`) as soc
FROM luxpower
WHERE `Minutes_Since_Midnight` <= 420 
GROUP BY `Date_MySQL`) query2 ON query1.`Date_MySQL` = query2.`Date_MySQL` ORDER BY query1.`Date_MySQL`;

    The first query checks where Minutes_Since_Midnight is less or equal to 2am while the second checks where it is less or equal to 7am.

    You can read more here https://dev.mysql.com/doc/refman/8.4/en/all-subqueries.html

    Login or Signup to reply.
  2. 0

    Your two queries should be raising errors as soc is not in the GROUP BY clause or in an aggregate function. The fact that they are "working" means you have ONLY_FULL_GROUP_BY disabled, which is a bad thing. Please read MySQL Handling of GROUP BY, particularly the bit explaining nondeterministic results.

    Sample data

    Date_MySQL Minutes_Since_Midnight soc
    2024-06-01 108 13
    2024-06-01 115 9 want this one as <= 120
    2024-06-01 121 12
    2024-06-01 413 8
    2024-06-01 419 7 want this one as <= 420
    2024-06-01 423 9
    2024-06-02 118 16
    2024-06-02 120 15 want this one as <= 120
    2024-06-02 413 18
    2024-06-02 420 17 want this one as <= 420

    The currently accepted answer would output:

    Date_MySQL socama socamb
    2024-06-01 13 13
    2024-06-02 16 18

    These are incorrect, as it is simply returning the maximum soc in the given group, as opposed to the soc for the maximum Minutes_Since_Midnight in the group.

    There are lots of ways to achieve this, one being to use correlated subqueries in the select list:

    SELECT l.Date_MySQL,
    (
        SELECT soc FROM luxpower
        WHERE Date_MySQL = l.Date_MySQL
        AND Minutes_Since_Midnight <= 120
        ORDER BY Minutes_Since_Midnight DESC LIMIT 1
    ) AS socama,
    (
        SELECT soc FROM luxpower
        WHERE Date_MySQL = l.Date_MySQL
        AND Minutes_Since_Midnight <= 420
        ORDER BY Minutes_Since_Midnight DESC LIMIT 1
    ) AS socamb
FROM luxpower l
GROUP BY l.Date_MySQL;

    Outputs:

    Date_MySQL socama socamb
    2024-06-01 9 7
    2024-06-02 15 17

    Another way is to use conditional aggregation to get the two max times (<= 120 and <= 420) and then use them to join back to retrieve the soc:

    SELECT t1.Date_MySQL, l1.soc AS socama, l2.soc AS socamb
FROM (
    SELECT
        Date_MySQL,
        MAX(IF(Minutes_Since_Midnight <= 120, Minutes_Since_Midnight, NULL)) AS t_120,
        MAX(Minutes_Since_Midnight) AS t_420
    FROM luxpower
    WHERE Minutes_Since_Midnight <= 420
    GROUP BY Date_MySQL
) AS t1
JOIN luxpower l1 ON t1.Date_MySQL = l1.Date_MySQL AND t1.t_120 = l1.Minutes_Since_Midnight
JOIN luxpower l2 ON t1.Date_MySQL = l2.Date_MySQL AND t1.t_420 = l2.Minutes_Since_Midnight;

    Same output as previous query.

    You could also achieve the same using the ROW_NUMBER() window function and then conditional aggregation to pivot the result:

    WITH t1 AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Date_MySQL ORDER BY Minutes_Since_Midnight <= 120 DESC, Minutes_Since_Midnight DESC) AS rn2,
        ROW_NUMBER() OVER (PARTITION BY Date_MySQL ORDER BY Minutes_Since_Midnight DESC) AS rn7
    FROM luxpower
    WHERE Minutes_Since_Midnight <= 420
)
SELECT
    Date_MySQL,
    MAX(IF(rn2 = 1, soc, NULL)) AS socama,
    MAX(IF(rn7 = 1, soc, NULL)) AS socamb
FROM t1
WHERE rn2 = 1 OR rn7 = 1
GROUP BY Date_MySQL;

/* or */

WITH t1 AS (
    SELECT Date_MySQL, Minutes_Since_Midnight AS MSM, soc,
        ROW_NUMBER() OVER (PARTITION BY Date_MySQL, IF(Minutes_Since_Midnight <= 120, 't_120', 't_420') ORDER BY Minutes_Since_Midnight DESC) AS rn
    FROM luxpower
    WHERE Minutes_Since_Midnight <= 420
)
SELECT
    Date_MySQL,
    MAX(IF(rn = 1 AND MSM <= 120, soc, NULL)) AS socama,
    MAX(IF(rn = 1 AND MSM >  120, soc, NULL)) AS socamab
FROM t1
WHERE rn = 1
GROUP BY Date_MySQL;

    Same output again.

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search