how do two groups aggregate the sum of two queries?

Presumably, there are several rows in each of the two tables for each year, and you want to sum them.

Here is one approach using lateral joins:

select y.yr, coalesce(a.price, 0) + coalesce(b.price, 0) price
from (values (2017), (2018), (2019), (2020)) as y(yr)
cross apply (
    select sum(a.price) total_price 
    from apples a
    where a.date >= datefromparts(y.yr, 1, 1) and a.date < datefromparts(y.yr + 1, 1, 1)
) a
cross apply (
    select sum(b.price) total_price 
    from bananas b
    where b.date >= datefromparts(y.yr, 1, 1) and b.date < datefromparts(y.yr + 1, 1, 1)
) b

An important thing to note is that this query optimizes the lookups in the two tables: it would take advantage of indexes on the date columns. It also allows missing years in any of the two tables.

If there is just one row per year, in each of the two tables, then we can dispense aggregation, and use straight left joins:

select y.yr, coalesce(a.price, 0) + coalesce(b.price, 0) price
from (values (2017), (2018), (2019), (2020)) as y(yr)
left join apples a
    on  a.date >= datefromparts(y.yr, 1, 1) 
    and a.date < datefromparts(y.yr + 1, 1, 1)
left join bananas b
    on  b.date >= datefromparts(y.yr, 1, 1) 
    and b.date < datefromparts(y.yr + 1, 1, 1)

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top