Joins bothering me again !!! - 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

New to SQL Server Programming

Joins bothering me again !!!

Author

Topic

kid_on_the_block
Posting Yak Master

172 Posts

Posted - 2005-12-28 : 00:12:23

Hi

I have the following table ,
----------------------------
Code Particulars
----------------------------
A xxxx
B yyyy
A xxxx
B yyyy
B zzzz

And

CodeTable
-----------------
A
B
C

Now I want a count of the above table

I do Select count(code),code from table1 group by code

I get the following result

2 A
3 B

I want the result like

2 A
3 B
0 C

I have tried using joins but am unable to so so plz help.

Regards
Kid

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-28 : 00:20:40

try this..
select code,countCode=(select count(particulars) from CodeParticulars b where a.code = b.code) from CodeTable a

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-28 : 00:46:37

or

Select count(T1.code),T2.code from CodeTable T1 left join CodeParticulars T2 on t1.code=T2.code
group by T2.code

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources