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

0

I am making an elastix bind report and I have a problem here in the output. Theoretically, if I do not 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;

    
asked by anonymous 20.02.2014 / 20:13

1 answer

3

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

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

Instead of using distinct , review your use of joins, in an attempt to avoid duplicate items.

Other than that, I was unable to identify exactly what your question would be.

    
20.02.2014 / 20:36