We help IT Professionals succeed at work.

Singles Or Partners

wdarnellg
wdarnellg asked
on
Hi Experts,
I need to get a count from a sql server 2008 database table that has two fields that relates to a second table. I am trying avoid creating two separate views because I need to show the data reflected in a pie chart. One column needs to be 'SingleFamily' and the other 'PartneredFamily'.
Please provide an example or advise.
Thanks
SELECT       COUNT((Select dbo.sj_players.ParentID from dbo.sj_Players where dbo.sj_Players.OtherParentID = null or dbo.sj_Players.OtherParentID = 0) )[SingleFamily], COUNT(p.OtherParentID)[PartneredFamily]
FROM         dbo.sj_Players AS p INNER JOIN
                      dbo.sj_ParentGuardian AS pa ON p.ParentID = pa.ParentID LEFT OUTER JOIN
                      dbo.sj_ParentGuardian AS ps ON p.OtherParentID = ps.ParentID	
GROUP BY	p.ParentID, p.OtherParentID

Open in new window

Comment
Watch Question

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
do a SUM( case when ... then 1 else 0 end)

instead of COUNT(*)...

Author

Commented:
I have never used a Sum-Case statement, and I am having trouble making it work. Could you elaborate a little more please.

Author

Commented:
Ok, What I have done so far is in the code. I tried to use the ParentID fields but the Sum function just seemed to add all of the ParentID integers instead of summing the fields. The current code also fails to give me a count of the fields as it is only reading the string content.
Select 
	SUM(CASE WHEN OtherParentID > 0 THEN pg.LastName ELSE 0 END) AS PartneredFamily,
	SUM(CASE WHEN OtherParentID < 1 or OtherParentID IS NULL THEN pg.LastName Else 0 END) AS SingleFamily
	
	FROM
	dbo.sj_Players AS p INNER JOIN dbo.sj_ParentGuardian AS pg ON p.ParentID = pg.ParentID LEFT OUTER JOIN dbo.sj_ParentGuardian AS opg 
	ON p.OtherParentID = opg.ParentID

Open in new window

Author

Commented:
Ok, this is closer, but it is not filtering the OtherParentID. I should get a different count for the records that have an ID >0 than the ID that <> 0 or is null.
Select 
	COUNT(CASE WHEN P.OtherParentID > 0 THEN pg.LastName ELSE 0 END) AS PartneredFamily,
	COUNT(CASE WHEN P.OtherParentID <> 0 or P.OtherParentID IS NULL THEN pg.LastName Else 0 END) AS SingleFamily
	
	FROM
	dbo.sj_Players AS p INNER JOIN dbo.sj_ParentGuardian AS pg ON p.ParentID = pg.ParentID LEFT OUTER JOIN dbo.sj_ParentGuardian AS opg 
	ON p.OtherParentID = opg.ParentID

Open in new window

Author

Commented:
OK Angel, I got it. Putting the one in the THEN part got the sum to be correct. Thanks, it turns out I made it harded than it had to be.
Select 
	Sum(CASE WHEN P.OtherParentID > 0 THEN 1 ELSE 0 END) AS PartneredFamily,
	Sum(CASE WHEN P.OtherParentID  IS NULL OR p.OtherParentID = 0   THEN 1 Else 0 END) AS SingleFamily
	
	FROM
	dbo.sj_Players AS p INNER JOIN dbo.sj_ParentGuardian AS pg ON p.ParentID = pg.ParentID LEFT OUTER JOIN dbo.sj_ParentGuardian AS opg 
	ON p.OtherParentID = opg.ParentID

Open in new window

Author

Commented:
I was not sure which field to put in the When portion, but I was at least prompted to google a little more effectively. I saw the answer accurately when I realized which field went where.
Thanks.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
glad I could help