Singles Or Partners
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
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.OtherParentIDASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.ParentIDASKER
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.ParentIDASKER
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.ParentIDASKER
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.
Thanks.
glad I could help
ASKER