MYSQL: Sum issue

Question

I am having a small issue where my life are counting wrong at the top but at the bottom of the while fetch array they are all correct.

They are stored in the DB as the following

life = 1 IPP =1 Funeral =1

the query should check to see if the row matches the 1 and count it.

 mysql_query("SELECT DISTINCT consultant, COUNT(*) as total, 
        sum(CASE WHEN life = '1' then 1 else 0 end) life,
        sum(CASE WHEN funeral = '1' then 1 else 0 end) funeral,
        sum(CASE WHEN IPP = '1' then 1 else 0 end) IPP
         FROM new WHERE call_date = '".$date."' and `qc_status` != 'Fail' GROUP BY consultant ORDER BY total DESC, life DESC, funeral DESC");

I have a staff member who life total is 9 however when I ran the script above it = 8

when i look further down the list I have a staff member on 4 and in the DB it is 4.

Full script.
{
        $totallife=0;
        $totalfuneral;
        $totalIPP;
        $total =0;
        $agents = mysql_query("SELECT DISTINCT consultant, COUNT(*) as total, 
        sum(CASE WHEN life = '' then 0 else 1 end) as life,
        sum(CASE WHEN funeral = '1' then 1 else 0 end) as funeral,
        sum(CASE WHEN IPP = '1' then 1 else 0 end) IPP
         FROM new WHERE call_date = '".$date."' and `qc_status` != 'Fail' GROUP BY consultant ORDER BY total DESC, life DESC, funeral DESC");
        while($agent = mysql_fetch_array($agents)){
            $totallife = $totallife+$agent[life];
            $totalfuneral = $totalfuneral+$agent[funeral];
            $totalIPP = $totalIPP+$agent[IPP];
            $total= $total+$agent[total];
            echo "<tr><td>".$agent[consultant]."</td><td>".$agent[life]."</td><td>".$agent[funeral]."</td><td>".$agent[IPP]."</td><td>".$agent[total]."</td></tr>";
        }

        echo "<tr><td><b>TOTALS</b></td><td><b>".$totallife."</b></td><td><b>".$totalfuneral."</b></td><td><b>".$totalIPP."</b></td><td><b>".$total."</b></td></tr>";
    }

Answer 1

Remove the DISTINCT from the SELECT since you already are grouping by that field consultant :

SELECT 
    consultant, 
    COUNT(*) as total, 
    sum(CASE WHEN life = '1' then 1 else 0 end) life,
    sum(CASE WHEN funeral = '1' then 1 else 0 end) funeral,
    sum(CASE WHEN IPP = '1' then 1 else 0 end) IPP
FROM 
    new 
WHERE 
    call_date = '".$date."' 
    and `qc_status` != 'Fail' 
GROUP BY 
    consultant 
ORDER BY 
    total DESC, 
    life DESC, 
    funeral DESC