how to use the subquery - 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

General SQL Server Forums

Database Design and Application Architecture

how to use the subquery

Author

Topic

Syima
Starting Member

13 Posts

Posted - 2007-09-27 : 01:21:37

Hi there,

1. Assuming I have below table:
Projectid, Dept, Budgeted, Approved, Status
A1, Audit, Yes, No, Started
B1, HR, No, Yes, Started
C1, IT, Yes, Yes, Not Started
D1, Audit, Yes, Yes, Dropped

2. Below are the 2 queries created
select dept, count(projectid) from table where budgeted = 'yes' group by dept
select dept, count(projectid) from table where budgeted = 'yes' and (approved = 'yes' or status = 'started') group by dept

3. How to join the above 2 queries for me to get the following result

Dept, Budgeted, Exp1
Audit, 2, 1
IT, 1, 1
HR, 0, 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-27 : 01:29:37

[code]SELECT Dept,
Budgeted = SUM(CASE WHEN Budgeted = 'Yes' THEN 1 ELSE 0 END),
Exp1 = SUM(CASE WHEN Budgeted = 'Yes' AND (Approved = 'Yes' OR Status = 'Started') THEN 1 ELSE 0 END)
FROM table1
GROUP BY Dept[/code]

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

Syima
Starting Member

13 Posts

Posted - 2007-09-27 : 01:40:51

Hi there,

thx for d reply. it worx. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources