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.
| Author |
Topic |
|
kaushals
Starting Member
5 Posts |
 Posted - 2010-10-12 : 15:17:26
|
| Hi,I have two tablesTable 1: ProjectFields: projectid, projectnameTable 2: EmployeeFields: id, name, statusid, projectidStatusid can be "New", "Assigned", "Completed"I would like to get result in the format below.Project Total New Assigned CompletedABC0001 30 10 15 2DEF002 40 20 20 0I know how to get count using group by for projectname and toal but my question is how do I get the count for all different statusid for a particular project?Thanks in advance for your help. |
|
|
kaushals
Starting Member
5 Posts |
Posted - 2010-10-12 : 15:31:53
|
| I figured out the.I need to writeselect projectname, count(employee), count(case empstatus when 'New' then 1 end) as New, count(case empstatus when 'Assigned' then 1 end) and so on... and ofcourse i have to join the tables and group by....Thanks to previous posts which helped me to figure out.This forum rocks! |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-10-17 : 02:51:51
|
| you can also use PIVOT without using group by if you're using sql 2005 or more------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Group By + Count