Combining query results into 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

SQL Server 2000 Forums

SQL Server Development (2000)

Combining query results into one

Author

Topic

matta0990
Starting Member

44 Posts

Posted - 2011-09-12 : 03:53:45

rite, ive got a query that displays a number of results e.g

Volume Heat
OpeningBalance 10 20
Receipt1 15 5
Receipt2 22 10
Receipt3 10 30
ClosingBalance 14 50
OpeningBalance 10 20
Receipt1 15 5
Receipt2 22 10
Receipt3 10 30
ClosingBalance 14 50
OpeningBalance 10 20
Receipt1 15 5
Receipt2 22 10
Receipt3 10 30
ClosingBalance 14 50

......... right, how to i get these results to display like

OpeningBalance 90 60
Receipt1 45 15
Receipt2 66 30
Receipt3 30 90
ClosingBalance 42 150

So i can combine all the results of me query into one. N.b-- my results are based on a very big query that joins a number of diffrent tables together.

J Smith

Kristen
Test

22859 Posts

Posted - 2011-09-12 : 04:14:59

You have two column headings - Volume & Heat - but three columns of data output? OpeningBalance, 10 and 20 ?

I can't see how

OpeningBalance 90 60

in your summary is evaluated from

OpeningBalance 10 20
OpeningBalance 10 20
OpeningBalance 10 20

but if you are just trying to get an aggregate of the total of the numeric columns you need something like:


SELECT Col1, SUM(Col2) AS [Col2 Label], SUM(Col3) AS [Col3 Label]
FROM MyTable
GROUP BY Col1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 04:16:49

Just group by first column that contains OpeningBalance, closing balance etc as values (i dont know its name as its not specified in sample above)and take SUM(Volume) and SUM(Heat)

so it will be like


SELECT <firstcolumnnamehere>,SUM(Volume),SUM(Heat)
FROM
(
your current query here
)t
GROUP BY <firstcolumnnamehere>

i assume the posted value for opening balance is wrong (it should be 30 against 90 i guess)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

matta0990
Starting Member

44 Posts

Posted - 2011-09-12 : 04:21:32

yes sorry, OpeningBalance column is AccountLineType. And yes it should have said 30 instead of 90... will try that now

J Smith

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 04:23:23

so it will become


SELECT AccountLineType,SUM(Volume),SUM(Heat)
FROM
(
your current query here
)t
GROUP BY AccountLineType

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources