userian userian - 8 months ago 29
SQL Question

SQL (sqlite) compare sums of rows grouped by another repeating row

Sorry, didn't know how to better formulate the question.
I have a table like:

|day name trees_planted|
|1 | alice | 3 |
|2 | alice | 4 |
|1 | bob | 2 |
|2 | bob | 4 |

I'm using
SELECT name, SUM(trees_planted) FROM year2016 GROUP BY name
to get:

name | trees_planted
alice | 7
bob | 6

But then I have another table from 2015 and I want to compare the results with the previous year, if for example Alice planted more trees in 2016 than in 2015 I'd get a result like this:

name | tree_difference
alice | -2 (if previous year she planted 5 trees, 5 -7 = -2)
bob | 0 (planted the same number of trees last year)

Sadly I don't even know where to start for this, so any help would be greatly appreciated.


Assuming you can join using user field, you can do:

select,,, -
      (select name, SUM(trees_planted) tp from year2016 group by name) a
     inner join
      (select name, SUM(trees_planted) tp from year2015 group by name) b

If you can't join on field user (you have different set of users in 2015 and 2016), it'll be easy to add the missing information by using a couple of union clauses.

Here's a link with artificial data to SQLFIDDLE to try the query.