Trying to show Count of Person, Spouse, Kids-CodePudding

I am doing

select
    count(distinct(ssn))
    , count (distinct(ssn   spousename))
    , count distinct((ssn   spousename   kidname))

My problem is that if the spouse name or kid name are blank there is no spouse or kid so they should not be counted.

How would you bypass a blank value from the count?

data:


SSN        Person Spouse Child 

111-11.... John             (no spouse no kid) 

222-22.....Jane  Jim   Jack   

333-33.....Jerry       Jack (no spouse) 
333-33.....Jerry       Jill (no spouse second kid) 

444-44..... John Judy       (no kid)

My answer should be 4 people, 2 spouses and 3 kids because I am doing a count of unique values that don't include blank.

I can't show the real SSN and names so It looks like fake data Thank you!

CodePudding user response：

you may try COALESCE

select
    count(distinct(ssn))
    , count (distinct(ssn   spousename))
    , count distinct((ssn   spousename   coalesce(kidname,'')))

CodePudding user response：

Assuming BLANK and not NULL values

Declare @YourTable Table ([SSN] varchar(50),[Person] varchar(50),[Spouse] varchar(50),[Child] varchar(50))  Insert Into @YourTable Values 
 ('111-11.....','John','','')
,('222-22.....','Jane','Jim','Jack')
,('333-33.....','Jerry','','Jack')
,('333-33.....','Jerry','','Jill')
,('444-44.....','John','Judy','')
 

 
Select count(distinct (ssn))
      ,count(distinct (ssn   nullif(spouse,'')))
      ,count(distinct (ssn   nullif(spouse,'')   nullif(child,''))) 
 from @YourTable


(No column name)    (No column name)    (No column name)
4                   2                   1

CodePudding user response：

Try this:

SELECT
    COUNT(ssn) AS people,
    SUM(spouses) AS spouses,
    SUM(children) AS children
FROM
    (
    SELECT
        ssn,
        MAX(person) AS person,
        COUNT(spouse) AS spouses,
        COUNT(child) AS children
    FROM
        TableName
    GROUP BY
        ssn
    ) BySSN
;

CodePudding user response：

Providing your data in an accurate (and of course made up!), and consumable (DDL DML) format is critical to getting the correct answer.

Knowing whether you use null or blank is makes a big difference also.

I am a big fan of counting exactly what it is you want to count, rather than relying on distinct.

declare @MyData table (SSN varchar(6), Person varchar(12), Spouse varchar(12), Child varchar(12));

insert into @MyData (SSN, Person, Spouse, Child)
values
('111-11', 'John', null, null), 
('222-22', 'Jane', 'Jim', 'Jack'),   
('333-33', 'Jerry', null, 'Jack'), 
('333-33', 'Jerry', null, 'Jill'), 
('444-44', 'John', 'Judy', null);

with cte as (
    select count(Spouse) Spouse, count(Child) Child
    from @MyData
    group by SSN
)
select count(*) [Num People], sum(Spouse) Spouses], sum(Child) [Num Children]
from cte;

Returns:

Num People	Num Spouses	Num Children
4	2	3