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 |
|
nomadsoul
Yak Posting Veteran
89 Posts |
 Posted - 2006-10-31 : 18:04:13
|
| What kind of query should I use?:I have only one table called: Contracts, with fields called: , Department Employee and ContractNumber (that accepts null values) and if null, it means that the Employee did not sign a contract. For each department, I want to list the total number of Employees who signed a contract in one column and in another column the total number that didn't(the nulls) in another column.--Please tell me if I should use a self join or a subquery? or should I do something totally different?--------------------------------------------------------------------------*/It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-31 : 18:19:13
|
[code]select Department, [Employee with contract] = sum(case when ContractNumber is not null then 1 else 0 end), [Employee without contract] = sum(case when ContractNumber is null then 1 else 0 end)from Contractsgroup by Department[/code]
KH |
 |
|
|
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-10-31 : 18:27:48
|
| Thanks KThan, That is interesting,that's the first time I've seen an = used in the select. I am glad I don't have to use. I hope you don't mind if I paste this to my list of queries as a reference.It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-10-31 : 20:21:46
|
>>That is interesting,that's the first time I've seen an = used in the select. It is alias name. You can also useselect Department, sum(case when ContractNumber is not null then 1 else 0 end) as [Employee with contract], sum(case when ContractNumber is null then 1 else 0 end) as [Employee without contract] from Contractsgroup by Department Also read about Cross-tab Reports in sql server help fileMadhivananFailing to plan is Planning to fail |
|
|
|
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-10-31 : 21:28:10
|
| Thanks, didn't know that about the equal signIt is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
|
|
|
|
|
Should I use a subquery or a self join?