Add values from 2 querys

Question

0.00/5 (No votes)

See more:

Hello everyone,
I have a problem. Im doing a report that shows our current sum of orders. However i have a problem since the datarows in my local currency are having a VAT value added and i need to get these without that value. So i need to get order sold in DKK in one Query and orders sold in EUR and USD in another Query.
I have tried with a UNION ALL function but my 2016 numbers are not added together.
What am i doing wrong here?

Im getting this result when i run the Query

CurrentOrders CurrentYear
100800	        2016
49402,44		2015
659333,06		2016

I was aiming for the 2016 values to be added to eachother

What I have tried:

SQL

/*Getting orders in DKK currency without VAT*/

(SELECT        SUM(Amount) AS CurrentOrders, YEAR([Order Date]) AS CurrentYEAR
FROM            [Sales Header]
WHERE        ([Document Type] = 1) AND (Code4 = 'SLAM')AND ([Currency Code]= 'DKK')
Group BY YEAR([Order Date])
)
UNION ALL
(
/*Getting orders in EUR and USD currency without VAT*/
SELECT        SUM([Outstanding Amount ($)]) AS CurrentOrders, YEAR([Order Date]) AS CurrentYEAR
FROM            [Sales Header]
WHERE        ([Document Type] = 1) AND (Code4 = 'SLAM')AND ([Currency Code]<> 'DKK')
Group BY YEAR([Order Date])
)

Posted 29-Mar-16 4:17am

Kristian_dk

Updated 29-Mar-16 6:01am

Andy Lanng

v2

Add a Solution

Comments

RickZeeland 29-Mar-16 10:37am

UNION ALL does not sum values, it adds rows !
It would not be wise to add DKK and Dollars anyway, so I think your results are ok.

Kristian_dk 29-Mar-16 11:05am

Thank you for replying. I also believe that my results are okay, but how do i get the 2016 results added in the end. I have already ensured in my Querys that the results are now in DKK without VAT so i simply need to get
THIS:
100800 : 2016
49402,44 : 2015
659333,06 : 2016

TO BE LIKE THIS
49402,44 : 2015
760133,06 : 2016

CHill60 29-Mar-16 11:29am

The first part of your query does that...
SELECT SUM(Amount) AS CurrentOrders, YEAR([Order Date]) AS CurrentYEAR
FROM [Sales Header]
WHERE [Document Type] = 1 AND Code4 = 'SLAM' AND [Currency Code]= 'DKK'
Group BY YEAR([Order Date])

Kristian_dk 29-Mar-16 11:53am

The problem is that the query currently returns the results from each SELECT in two datarows:
100800 : 2016
659333,06 : 2016
I need only one row with the two numbers added to eachother.
Any ideas?

CHill60 29-Mar-16 11:56am

Remove the Group By all together, but you did actually state that you wanted two rows of results in your comment

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)

ZurdoDev · Accepted Answer · 2016-03-29T06:01:00

An easy way to do it is to view your results as a table itself. Then group by on that and SUM. For example, wrap your existing sql as a derived table:

SQL

SELECT SUM(x.CurrentOrders), x.CurrentYear
FROM (
  (SELECT        SUM(Amount) AS CurrentOrders, YEAR([Order Date]) AS CurrentYEAR
  FROM            [Sales Header]
  WHERE        ([Document Type] = 1) AND (Code4 = 'SLAM')AND ([Currency Code]= 'DKK')
  Group BY YEAR([Order Date])
  )
  UNION ALL
  (
  /*Getting orders in EUR and USD currency without VAT*/
  SELECT        SUM([Outstanding Amount ($)]) AS CurrentOrders, YEAR([Order Date]) AS   CurrentYEAR
  FROM            [Sales Header]
  WHERE        ([Document Type] = 1) AND (Code4 = 'SLAM')AND ([Currency Code]<> 'DKK')
  Group BY YEAR([Order Date])
  )
) x
GROUP BY x.CurrentYear