additional data into grouping - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

additional data into grouping

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-07 : 22:15:29

Query1
--------
SELECT MONTH(date)as date, sum(qty) as qty
FROM #TempSt_trx
WHERE date>='2007-01-01 00:00:00.000' and date<='2007-12-31 00:00:00.000'
GROUP BY date

Query 1 will generate result into
date qty
1 10
2 20
3 15
5 16
6 70

Query 2
SELECT SUM(qty)as qty FROM #TempSt_trx where date <'2007-01-01 00:00:00.000'

Query 2 will generate result into
165

How i going to combine both result into
date qty
1 10
2 20
3 15
5 16
6 70
13 165
coz Query 2 is the balance in previous year. Query 1 will group all records into month and i wanna display query2 as month 13 with the sum

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-07 : 22:18:22

use union all


SELECT MONTH(date)as date, sum(qty) as qty
FROM #TempSt_trx
WHERE date>='2007-01-01 00:00:00.000' and date<='2007-12-31 00:00:00.000'
GROUP BY date

union all

SELECT 13 as date, SUM(qty)as qty FROM #TempSt_trx where date <'2007-01-01 00:00:00.000'

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-07 : 22:18:28

select * from(
SELECT MONTH(date)as date, sum(qty) as qty
FROM #TempSt_trx
WHERE date>='2007-01-01 00:00:00.000' and date<='2007-12-31 00:00:00.000'
GROUP BY date
union
SELECT 13 as date, SUM(qty)as qty FROM #TempSt_trx where date <'2007-01-01 00:00:00.000')a

woops done

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-07 : 22:21:02

khtan...could u help me in my sto-pro...>"<
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127101

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-07 : 22:32:30