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

Can I combine these two Mysql queries?

I have a database with a lot of data in it already that is seperated by state, year, and quarter. So the DB layout is something like this:

jurisdiction total_miles quarter year
AL 300 3 2022
AL 500 3 2022
AL 200 4 2022
AL 100 1 2023
AL 50 2 2023

These two separate queries work by themselves to get the total from the quarters 3 and 4 from 2022 and quarters 1 and 2 from 2023:

SELECT sum(total_miles) FROM db_table WHERE quarter >= 3 AND year = '2022' AND jurisdiction = 'AL';
SELECT sum(total_miles) FROM db_table WHERE quarter <= 2 AND year = '2023' AND jurisdiction = 'AL';

I tried to combine the two queries by using the query

SELECT sum(total_miles) FROM db_table WHERE (quarter >= 3 AND year = '2022') AND (quarter <= 2 AND year = '2023') AND jurisdiction = 'AL'

But I get a NULL return value for sum(total_miles). I tried using variables to try and add up the values and it gives me a different number than the actual total for the state. Is there a way to correctly write the query so that it sums up the total miles from quarters 3 and 4 of 2022 and quarters 1 and 2 of 2023?

Tags:

2

Answers


  1. 0

    If you’d like the combined mileage for both periods, then here’s how to do it:

    SELECT
    SUM(`total_miles`) AS `miles`
FROM
    `db_table`
WHERE
    (
        (`year` = '2022') AND
        (`quarter` >= 3)
    ) OR
    (
        (`year` = '2023') AND
        (`quarter` <= 2)
    )
;

    Result:

    miles
    1150

    If, instead, you’d like the miles for each period separately, but using a single query, then here’s how to do that:

    SELECT
 (
        SELECT
            SUM(`total_miles`)
        FROM `db_table`
        WHERE
            (`quarter` >= 3) AND
            (`year` = '2022')
    ) AS `2022b`,
    (
        SELECT
            SUM(`total_miles`)
        FROM `db_table`
        WHERE
            (`quarter` <= 2) AND
            (`year` = '2023')
    ) AS `2023a`;

    Here’s the result:

    2022b 2023a
    1000 150

    NOTE: These queries use tabs and newlines that may causes issues for you. If that’s the case, try starting your mysql client with the --disable-auto-rehash option, like this:

    mysql --disable-auto-rehash

    Alternatively, you can strip the whitespace from the queries instead.

    Login or Signup to reply.
  2. 0

    As stated in the comments, you can fix your query by making it

    SELECT sum(total_miles)
FROM db_table
WHERE (quarter >= 3 AND year = '2022') OR (quarter <= 2 AND year = '2023') AND jurisdiction = 'AL'

    Alternatively, since the column names match, you could use UNION

    
SELECT
  sum(total_miles) as miles,
  year
FROM
  db_table 
WHERE quarter >= 3 AND year = '2022' AND jurisdiction = 'AL'
UNION
SELECT
  sum(total_miles) as miles, 
  year
FROM
  db_table
WHERE quarter <= 2 AND year = '2023' AND jurisdiction = 'AL';

    Like Andy’s answer, this will give you both numbers in one result (instead of the total), but with the data in rows instead of columns

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

Back To Top
Search