0

recently started with simple queries in SQL

I was wondering if there is a possibility to add data vertically like "union" does horizontally

I have two selection i want to combine

month year data1 data2 grouped by month, year
month year data3 data4 grouped by month, year

what I'm trying to archive is 

month year data1 data2 data3 data4

Is there any function working like union to archive this? I was trying to do so with "join" but failed horribly

Brochen
  • 3
  • 3
  • 1
    Please **[edit]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [Formatted text](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). ([edit] your question - do **not** post code or additional information in comments) –  Aug 27 '18 at 12:04
  • 3
    Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... –  Aug 27 '18 at 12:04
  • 1
    Can you show us what your query so far? Your question is vague – Glenn Ferrie Aug 27 '18 at 12:04
  • You are looking for join operator – amd Aug 27 '18 at 12:07
  • Are you looking to do something like dynamically increase the size of your `SELECT` list? or are you looking to do something like increase the number of `records` returned using a `UNION ALL`? – Daniel L. VanDenBosch Aug 27 '18 at 12:07
  • imo, [join](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) is the way to go. If it's the case, you can group both selections before joining the data. – Felypp Oliveira Aug 27 '18 at 12:12
  • A vertical union? That's a join! – The Impaler Aug 27 '18 at 13:43

1 Answers1

1

Sounds like if a FULL JOIN is probably what you want. It include all rows from both operands combining rows, that fulfill the expression in the ON clause. If there is no matching partner row in the other operand, the columns of the other operand are filled with NULL. That's why coalesce() is needed to get the year or month.

SELECT coalesce (x1.year, x2.year) year,
       coalesce (x1.month, x2.month) month,
       x1.data1,
       x1.data2,
       x2.data3,
       x2.data4
       FROM (<your first grouping query>) x1
            FULL JOIN (<your second grouping query>) x2
                      ON x2.year = x1.year
                         AND x2.month = x1.month;

Replace <your first grouping query> and <your second grouping query> with your first or second query, respectively.

sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • If you use `full join ... using (year, month)` then then there is no need for the `coalesce()` and the join columns will also only appear once in the output –  Aug 27 '18 at 12:30
  • This one dear sir is working like a charm. After 2 hours of frustration you really made my day! – Brochen Aug 27 '18 at 12:39