MySQL Get counts in subquery without grouping -

i've been trying find solution having count appear each entry there duplicate. every query i've tried either groups entries 1 row or 1 count incorrect.

here table without count:

------------------------------------------------------ | id | last_name | last_4_ssn | type | complete_date |  ------------------------------------------------------ | 1  | test      | 1234       | cc   | null          | | 2  | test      | 1234       | cc   | null          | | 3  | test      | 1234       | cc   | 2016-09-11    | | 4  | test      | 1234       | cc   | 2016-09-06    | | 5  | test      | 1234       | cc   | null          | | 6  | test      | 1234       | fe   | 2016-08-30    | | 7  | test      | 1234       | fe   | 2016-09-01    | | 8  | test      | 1234       | fe   | 2016-09-02    | ------------------------------------------------------ 

i'm trying count checks 'last_name', 'last_4_ssn' , 'type' complete_date not null.

i resulting table this:

-------------------------------------------------------------- | id | last_name | last_4_ssn | type | complete_date | count |  -------------------------------------------------------------- | 1  | test      | 1234       | cc   | null          | 0     | | 2  | test      | 1234       | cc   | null          | 0     | | 3  | test      | 1234       | cc   | 2016-09-11    | 2     | | 4  | test      | 1234       | cc   | 2016-09-06    | 2     | | 5  | test      | 1234       | cc   | null          | 0     | | 6  | test      | 1234       | fe   | 2016-08-30    | 3     | | 7  | test      | 1234       | fe   | 2016-09-01    | 3     | | 8  | test      | 1234       | fe   | 2016-09-02    | 3     | -------------------------------------------------------------- 

notice how there 2 cc complete dates , 3 fes complete dates? count trying get; last_name, last_4_ssn , type equal same , complete_dates not null.

the current sql trying (but not quite there):

select      clients.id,      clients.session_type,      clients.last_name,      clients.complete_date,      clients.last_4_ssn,      dups.the_count clients left join (     select id,         count(*) the_count,          session_type,          last_4_ssn,          last_name,          complete_date     `clients`               group last_name, last_4_ssn, session_type     having the_count > 1 , complete_date not null ) dups on (dups.last_4_ssn = clients.last_4_ssn , dups.last_name = clients.last_name , dups.session_type = clients.session_type) group clients.id 

any ideas appreciated. time!

select t1.*,        coalesece(t2.count, 0)              -- null dates should show 0 count clients t1 left join (     select last_name,            last_4_ssn,            type,            sum(case when complete_date not null 1 else 0 end) `count`     clients     group last_name,              last_4_ssn,              type ) t2     on t1.last_name  = t2.last_name ,    -- join on name, ssn, , type        t1.last_4_ssn = t2.last_4_ssn ,        t1.type       = t2.type ,        t1.complete_date not null        -- join count if date not null 

demo here:

sqlfiddle