Different result between select, after applying SUM() and Grop By

Question

I’m making a call report from elastix and I have a problem here in the outcome. Theoretically, if I don’t touch Where, the result would have to be the same.

select distinct
(case left( devices.id ,2)
when '10' then 'SAC'
when '40' then 'SAC'
when '41' then 'SAC'
when '80' then 'OUTROS'
when '70' then 'OUTROS'
when '90' then 'RECEPCAO'
when '91' then 'ADMINISTRATIVO'
when '92' then 'COMERCIAL'
when '93' then 'T.I.'
when '95' then 'SAC'
when '98' then 'PROJETOS'
when '99' then 'DIRETORIA DE T.I.'
else ' ' end) AS  grupo ,
round(sum(cdr.duration)/60)   AS  "duracao

from ((cdr  join  devices  on(( devices.dial  = substring_index(cdr.channel ,'-',1))))
join  trunks  on((substring_index(cdr.lastdata ,'/',2) = substring_index( trunks.channelid ,'/',2))))

where outcid = '12121212' and
!(left(cdr.dst,4) = 0800) and
(char_length(cdr.dst) > 4) and
cdr.duration > 60 and calldate >= '2014-02-01' and calldate <=  '2014-02-20'
group by grupo with rollup;

Answer 1

When you define group by grupo You ask mysql to display your grouped results, that is, there will be only one return for each group type.

And the SUM() sum the "duration" values of each of the items that make up each group.

Instead of using the distinct, review the use you are making of the joins, in order to avoid duplicated items.

Other than that, I couldn’t identify exactly what your question would be.