combine 2 select statement - 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 2000 Forums

Transact-SQL (2000)

combine 2 select statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-27 : 07:45:20

nottisue writes "hi..
i was wondering.. how to combine 2 select statements into one and return the result in one table
eg:
millName sum_this_week
----------- ------------------
a101 20
b101 30

millName sum_last_week
---------- -------------------
a101 23
b101 40

where actually it involved 4 dates
select millName, sum(KERNEL) as sum_this_week from millTable where millTable.[date] between '"&start_date_this_week&"' and "'&end_date_this_week&"' group by millName

and

select millName, sum(KERNEL) as sum_this_week from millTable where millTable.[date] between '"&start_date_last_week&"' and "'&end_date_last_week&"' group by millName

[*above is just a sample code]

so that the final result will be

millName sum_this_week sum_last_week
----------- ----------- -------------
a101 20 23
b101 30 40

please help me ASAP
thank you.."

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-01-27 : 08:56:17

[code]select millName, sum(KERNEL) as sum_this_week, d.sum_this_week as sum_last_week
from millTable
join
(
select millName, sum(KERNEL) as sum_this_week
from millTable
where millTable.[date] between '"&start_date_last_week&"' and '"&end_date_last_week&"'
group by millName
) d on d.millname = millTable.millname

where millTable.[date] between '"&start_date_this_week&"' and '"&end_date_this_week&"'
group by millName[/code]

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-27 : 17:55:47

Think it will be faster than two simple sum(case ... end)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources