CODEWITHSUNDEEP
mysqlaggregate-functions
Jorge Zapata
Jorge Zapata

Reputation: 2336

Aggregate functions in 2 tables with dates in common

I have the following 2 tables

id   km    date
1    22.1  2012-04-15
2    52.1  2012-04-14
3    72.1  2012-03-15
4    54.1  2012-03-14

and

id   lt    date
1    16.4  2012-04-03
2    22.6  2012-04-29
3    45.9  2012-03-2
4    13.1  2012-03-31

From this tables I need to get a rate by month, I mean, the number of km divided by number of lt in a month. I know I can get the sum of km by month using aggregate functions in a query and in other query the sum of lt, something like this.

SELECT SUM(km) FROM kilometers GROUP BY MONTH(date)
SELECT SUM(lt) FROM gasoline GROUP BY MONTH(date)

Then I could manually divide km/lt for each grouped month. So the question is, can I do that on a simple query? Do I have to change the structure of my database?

Any help would be appreciated, thanks.

Upvotes: 0

Views: 224

Answers (1)

mellamokb
mellamokb

Reputation: 56769

You should be able to just join the table data together by the month/year. You would just need to handle the situation where lt is 0 or null for a month. Note that you will also want to group by YEAR so it handles multi-year data appropriately:

SELECT k.year, k.month, k.km / g.lt
FROM (SELECT YEAR(date) as year, MONTH(date) as month, SUM(km) as km
      FROM kilometers GROUP BY YEAR(date), MONTH(date)) k
JOIN (SELECT YEAR(date) as year, MONTH(date) as month, SUM(lt) as lt
      FROM gasoline GROUP BY YEAR(date), MONTH(date)) g
  ON k.month = g.month AND k.year = g.year

Sample output:

YEAR  MONTH  K.KM / G.LT
----  -----  --------------
2012  3      2.138982929974
2012  4      1.90256407322

Demo: http://www.sqlfiddle.com/#!2/c2942/6

Upvotes: 1

Related Questions