Join 3 aggregate queries in one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join 3 aggregate queries in one

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-10 : 11:37:36

I have 3 indipendent Queries that return for the 10 SalesUnits we have the figures for Budget, SalesOrder and Turnover. Each Query has a SUM aggregate function and is grouped by SalesUnit. How can I get these 3 Queries in one view? Like:


SalesUnit|Budget|SalesOrder|Turnover|
-------------------------------------
Area 01  | xx1.x|  yy1.yy  | zz1.zz |
Area 02  | xx2.x|  yy2.yy  | zz2.zz |
   .
   .
Area 10  | x10.x|  y10.yy  | z10.zz |

Budget:

SELECT SalesUnit, SUM(Budget) From TableBudget
GROUP BY SalesUnit
Order By SalesUnit

SalesOrder:

SELECT SalesUnit, Sum(SalesOrder) From TableSalesOrder
GROUP By SalesUnit
Order By SalesUnit

Turnover:

SELECT SalesUnit, Sum(Turnover) From TableTurnover
GROUP By SalesUnit
Order By SalesUnit

Anyone can help me with this?
Regards, Martin

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-10 : 11:41:57

SELECT SalesUnit, SUM(Budget) as Value From TableBudget
GROUP BY SalesUnit

Union ALL

SELECT SalesUnit, Sum(SalesOrder) as Value From TableSalesOrder
GROUP By SalesUnit

Union all

SELECT SalesUnit, Sum(Turnover) as Value From TableTurnover
GROUP By SalesUnit

Order By SalesUnit

singularity
Posting Yak Master

153 Posts

Posted - 2012-07-10 : 11:43:10

[code]
SELECT a.SalesUnit, a.Budget, b.SalesOrder, c.Turnover
FROM
(SELECT SalesUnit, SUM(Budget) as Budget From TableBudget
GROUP BY SalesUnit
Order By SalesUnit) a
JOIN
(SELECT SalesUnit, Sum(SalesOrder) as SalesOrder From TableSalesOrder
GROUP By SalesUnit
Order By SalesUnit) b ON a.SalesUnit = b.SalesUnit
JOIN
(SELECT SalesUnit, Sum(Turnover) as Turnover From TableTurnover
GROUP By SalesUnit
Order By SalesUnit) c ON a.SalesUnit = c.SalesUnit
[/code]

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-10 : 11:52:41

jleitao, this would build a table with 2 columns and 30 rows
singularity: you can't order in that way. The query throws an error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified

Martin

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-10 : 11:53:54

Singularity,

That's right, you just can't use "order by" in subqueries ;)

Put the order by at the end of the script

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-10 : 11:56:20

SELECT a.SalesUnit, a.Budget, b.SalesOrder, c.Turnover
FROM
(SELECT SalesUnit, SUM(Budget) as Budget From TableBudget
GROUP BY SalesUnit
) a
JOIN
(SELECT SalesUnit, Sum(SalesOrder) as SalesOrder From TableSalesOrder
GROUP By SalesUnit
) b ON a.SalesUnit = b.SalesUnit
JOIN
(SELECT SalesUnit, Sum(Turnover) as Turnover From TableTurnover
GROUP By SalesUnit
) c ON a.SalesUnit = c.SalesUnit
Order By a.SalesUnit

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-10 : 12:00:25

Yes, that works! Placing the order at the end does the trick!
Thank you guys!

singularity
Posting Yak Master

153 Posts

Posted - 2012-07-10 : 19:31:22

Yeah, sorry I just copied the queries from the original post and forgot to take the ORDER BYs out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources