Beaker Beaker - 15 days ago 4
SQL Question

concatenating columns from multiple unrelated 1-row resultsets (with group by)

I found this very similar question here and all I would like to do in addition to this is group by date. There is a date column present in each sub-query.

concatenating columns from multiple unrelated 1-row resultsets

Each sub-query will all contain the exact same range of dates. I have tried putting group by in each sub-query, and in the outer query, but I can't seem to make a row combining each sub-query for each date. I am using this query in Hive, but I believe any ANSI SQL will work here. Don't quote me on that though. My scenario seems to be a minor variation on the answers found in the link I posted, however I can't seem to make it work.

Here is one query posted at the link I attached above:

select A, B, C, D
from ( SELECT SUM(A) as A, SUM(B) as B FROM X ) as U
CROSS JOIN ( SELECT SUM(C) as C, SUM(D) as D FROM Y ) as V


How do I add a GROUP BY to this when each sub-query has a date column? Or is there a better way to achieve the same result?

Answer

Is this what you want?

select u.dte, A, B, C, D 
from (select dte, SUM(A) as A, SUM(B) as B
      from X
      group by dte
     ) u join
     (select dte, SUM(C) as C, SUM(D) as D 
      from Y
      group by dte
     ) v
     on u.dte = v.dte;