MySQL Query returning differently

Question

MySQL Query returning differently

Navigation

#1 by (1 votes)

0

I have the following Query :

select 
    MONTHNAME(ta2.change_time) as mes,
    MONTH(ta2.change_time) as numeroMes,
    case 
        when s2.name like '%Servidores' then cast(SUM(ta2.time_unit) as UNSIGNED) * 1.5  else cast(SUM(ta2.time_unit) as UNSIGNED)
    end as TEMPO_CORRETO
from 
    otrs.service s2,
    otrs.time_accounting ta2,
    otrs.ticket t2,
    otrs.ticket_type tt2
where
    t2.id = ta2.ticket_id and
    t2.type_id = tt2.id and
    t2.service_id = s2.id and
    ta2.change_time between '2018-04-01' and '2018-04-30' and
    t2.customer_id = 'ZSCHIMMER SCHWARZ' and
    tt2.name = 'Contrato PCH'
group by 
    t2.customer_id,
    s2.name
order by 
    numeroMes

But I'm having the following return:

ButIwouldliketogroupthesevaluesbymonth,havingoneresultslikethis:

I've tried everything but I have not got it yet, if you have an idea.

mysql

asked by anonymous 17.05.2018 / 20:54

1 answer

Add the first "n" elements of a geometric progression in Python Generate a list of sentences ordered by arraySort () in js

score 1 · Accepted Answer

You can simplify that expiry there in the third column (turn first, according to your requirement).

You need to group by the columns that will be treated as 'totalizers'.

SELECT 
    CAST(SUM(CASE WHEN s2.name LIKE '%Servidores' THEN ta2.time_unit * 1.5 ELSE ta2.time_unit END ) AS UNSIGNED) AS TEMPO_CORRETO
    MONTHNAME(ta2.change_time) AS MES,
    MONTH(ta2.change_time) AS NUMEROMES
FROM 
    otrs.service s2,
    otrs.time_accounting ta2,
    otrs.ticket t2,
    otrs.ticket_type tt2
WHERE
    t2.id = ta2.ticket_id AND
    t2.type_id = tt2.id AND
    t2.service_id = s2.id AND
    ta2.change_time between '2018-04-01' AND '2018-04-30' AND
    t2.customer_id = 'ZSCHIMMER SCHWARZ' AND
    tt2.name = 'CONTRATO PCH'
GROUP BY 
    MONTHNAME(ta2.change_time),
    MONTH(ta2.change_time)
ORDER BY 
    NUMEROMES

Important: This query is not addressing the possibility of having records from the same month in different years. See if this would not be the case for your business rule.