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 |
|
collie
Constraint Violating Yak Guru
400 Posts |
 Posted - 2004-12-12 : 03:22:17
|
| Hi,I have a query that joins 2 tables.Hasama table:HID SID1 12 13 24 35 46 57 68 3My student table has the following values:SID FAMILY1 12 23 34 85 76 1Here is my queryselect max(hasama.hid) as hid,count(CASE family WHEN '1' THEN 1 END) as Unmarried,count (case family when '2' then 1 end) as Sep,count (case family when '3' then 1 end ) as DivorceP,count (case family when '4' then 1 end ) as Orphan,count (case family when '5' then 1 end) as Dad,count (case family when '6' then 1 end) as Mom,count (case family when '7' then 1 end) as Divorced,count (case family when '8' then 1 end) as Nuclear,sum (1)as Totalfrom studentinner join hasama on hasama.sid=student.sidThe result that i get is:hid Unmarried Sep Divorce Orphan dad mom divorced nuclear total8 3 1 2 0 0 0 1 1 8However i want the result to be as follows:Unmarried Sep Divorce Orphan dad mom divorced nuclear total2 1 1 0 0 0 1 1 6 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-12 : 03:44:46
|
| I haven't looked closely at your code, but I tend to do these things using SUM rather than COUNT:select max(hasama.hid) as hid,SUM(CASE family WHEN '1' THEN 1 ELSE 0 END) as Unmarried,...Kristen |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-12-13 : 13:49:34
|
| The resultset you show is what you would get if you only selected on the student table and elimintated the Hasama table from your SELECT. Also eliminate the HID column which comes from Hasama.-----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering |
|
|
|
|
|
|
|
|
join 2 tables