CODEWITHSUNDEEP
mysqlsqlinner-join
user3187352
user3187352

Reputation: 47

Mysql merge total results from this year and the last quarter of last year?

I have never had to do this before and tried searching online and really do not even know what I am looking for. I have to display all of the results from this current year, but also merge those results with the last quarter of last year. So the query to display all results from this year is:

SELECT distinct(jurisdiction), total_miles FROM test_a WHERE year = '2021' ORDER BY year DESC, quarter DESC, jurisdiction ASC

and to include the results from last quarter is:

SELECT distinct(jurisdiction), total_miles FROM test_a WHERE year = '2020' AND quarter ='4'

The DB structure is:

quarter
year
jurisdiction
total_miles

an example output would be:

Q1 2021 TX 100 miles
Q1 2021 CA 20 miles
Q2 2021 TX 50 miles

Q4 2020 AL 500 miles

What would be the query to merge the results from this year and the quarter of last year? Even my first query is not correct since it will not sum up all miles from each quarter of the current year.

So if this year the TX jurisdiction had 100 miles and last year's quarter 4 had 50 miles, it would just result TX jurisdiction has 150 miles. It would total up all of the miles from each jurisdiction. Thanks for your time.

Upvotes: 1

Views: 31

Answers (1)

nbk
nbk

Reputation: 49375

The year 2021 You have to GROUP BY jurisdiction to get the total for that year the rest should be a INNER JOIN

SELECT t1.jurisdiction, t1.total_miles + t2.total_miles as totalmile 
FROM
(SELECT jurisdiction, SUM(total_miles) as total_miles FROM test_a WHERE year = '2021' GROUP BY jurisdiction) t1
INNER JOIN (SELECT distinct(jurisdiction), total_miles FROM test_a WHERE year = '2020' AND quarter ='4') t2 ON t1.jurisdiction = t2.jurisdiction
ORDER BY t1.jurisdiction ASC

Upvotes: 2

Related Questions