mysql - Counting Distinct values from Sql Table -

following view of table...

i getting hard time in getting desired result query.

my requirement looks below image

it should conditioned follows -- 1)within start date , end date.

2)no pending .

3)yes completed.

4)no+yes total.

5)based on 1 survey type.

this have tried , got result above mentioned 1 4 condition , how impliment 5 condition ?

select distinct userid   ,case      when [yes] null       0     else [yes]     end completed   ,case      when [no] null       0     else [no]     end pending   ,(     case        when [yes] null         0       else [yes]       end + case        when [no] null         0       else [no]       end     ) total (select distinct userid     ,surveystatus     ,count(parcelid) cnt   parcelallocationsurvivor   dateallocated >= '2013-08-01'     , dateallocated <= '2013-08-07'   group userid     ,surveystatus   ) p pivot(max(cnt) surveystatus in ([yes],[no])) pvt order userid 

can me out in it.

thanks in advance////

you add where clause subquery:

select userid, (case when [yes] null 0 else [yes] end) completed,        (case when [no] null 0 else [no] end) pending,        (case when [yes] null 0 else [yes] end +         case when [no] null 0 else [no] end        ) total (select userid, surveystatus, count(parcelid) cnt        parcelallocationsurvivor       dateallocated >= '2013-08-01' , dateallocated <='2013-08-07' ,             surveytype = 'survey1'       group userid, surveystatus      ) p pivot(max(cnt) surveystatus in([yes],[no])) pvt order userid; 

by way, select distinct redundant. not need them query.