How to join data from 2 different sql queries

Question

0.00/5 (No votes)

See more:

Hello everyone
I have a problem. I'm doing a turnover report by year. But we have changed our datastructure per 2016. How do i get our TOTALSALES per SalesYear when i want to differ the SQL Query if the salesyear is 2016 or later.

//My Query that gets sales before 2016:

SQL

SELECT        SUM([Cust. Ledger Entry ].[Sales (LCY)]) AS TotalSales, YEAR([Cust. Ledger Entry ].[Posting Date]) AS SalesYear
FROM[Cust. Ledger Entry ] INNER JOIN
Customer ON [Cust. Ledger Entry ].[Customer No.] = Customer.[No.]
WHERE        ([Cust. Ledger Entry ].[Customer No.] <> '80501608') AND (Customer.[Gen. Bus. Posting Group] IN ('OFFSH EU', 'OFFSH Ø', 'OFFSH DK', 'FISK ØV', 'FISK EU', 'FISK DK', 'ØVRIG ØV', 'ØVRIG EU', 'ØVRIG DK', 'SLAM EU', 'SLAM DK', 'SLAM ØV', 'FØDE DK', 'FØDE EU', 'FØDE ØV')) AND 
([Cust. Ledger Entry ].[Document Type] IN ('2', '3')) AND (YEAR([Cust. Ledger Entry ].[Posting Date]) < '2016')
GROUP BY YEAR([Cust. Ledger Entry ].[Posting Date])
ORDER BY SalesYear

//My Query that gets sales after 2016:

SQL

SELECT        SUM([Credit Amount] - [Debit Amount]) AS TotalSales, YEAR([Posting Date]) AS SalesYear
FROM            [G/L Entry]
WHERE        ([G/L Account No.] IN ('100210', '100220', '100230', '100260', '100270', '100280', '100310', '100320', '100330', '100360', 
                         '100370', '100380', '100410', '100420', '100430'))
GROUP BY YEAR([Posting Date])
ORDER BY SalesYear

How on earth do i get the data returned together?

What I have tried:

I have tried to use UNION but i can't get this working.

Posted 7-Mar-16 0:15am

Kristian_dk

Updated 7-Mar-16 0:26am

Maciej Los

v2

Add a Solution

Comments

Suvendu Shekhar Giri 7-Mar-16 6:19am

It would be great if you can replicate the structure and data in SQLFiddle and give us the link.

Kristian_dk 7-Mar-16 7:04am

I don't know SQL fiddle, but i will learn this for the NeXT time i post an question in here. Thanks for submitting to my question.

Maciej Los 7-Mar-16 6:24am

UNIONA ALL

Mehdi Gholam 7-Mar-16 6:26am

UNION ALL :)

Maciej Los 7-Mar-16 6:28am

You're right, Mehdi. Simple typo ;)

Kristian_dk 7-Mar-16 7:03am

Thank you (both of you) for helping out. UNION ALL did the exact trick that i was looking for.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-03-07T00:26:00

Solution 1

Seems you want to combine two queries into single result. So, check this: UNION (Transact-SQL)[^]

SQL

[QUERY 1]
UNION ALL
[QUERY 2]

Note: the data type for each column must be the same!

Posted 7-Mar-16 0:26am

Maciej Los

Comments

Kristian_dk 7-Mar-16 7:02am

Thank you so much for helping out. This Works!! yehaa

Maciej Los 7-Mar-16 7:10am

You're very welome.
Cheers,
Maciej