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 |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2010-12-29 : 04:50:19
|
| when i run a sql with a group by like select count(id),school from users group by schoolnow i want this to show the school and number of users but I also want to see if a school has 0 usershow do I do this? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-29 : 04:55:36
|
| select count(u.id),s.school from schools sleft join userson s.school = u.schoolgroup by s.school==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-12-29 : 05:33:51
|
| a left join isn't doing it as if the school doesn't exist in my user table within my where clause then it doesn't return |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-29 : 05:42:51
|
| Which where clause?Move references to the users table from the where clause into the join clause.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-12-29 : 06:19:04
|
quote:
Originally posted by esthera
a left join isn't doing it as if the school doesn't exist in my user table within my where clause then it doesn't return
Can you post the query you used?MadhivananFailing to plan is Planning to fail |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-12-29 : 07:00:20
|
| the probelm is it's not as simple as i put it - I was tryting to amke simplerselect count(p.id) from users u left join teachers p on p.userid=u.id left join school s on s.schoolid=p.schoolid group by s.schoolidwhere mainprogram='xxxx'now if there is no record in teacher for that main program I want it to return 0 but it's not even returning the schoolid |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-12-29 : 08:22:18
|
| Try changing where to andMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
group by should show numbers with 0