Add return from select

Question

Add return from select

Navigation

#1 by (2 votes)

1

I have the following query :

SELECT
(case when day(sysdate()) < 10 and meses.mes = month(sysdate()) then 0 else
CAST(SUM(CASE WHEN s2.name LIKE '%Servidores' THEN ( coalesce(ta.time_unit,0) * 1.5)/ 60 ELSE coalesce(ta.time_unit,0)/60 END) AS DECIMAL(12, 3)) end) AS TEMPO_CHAMADO,
meses.customer_id as empresa
FROM
(Select distinct
  m.mes,
  t2.customer_id
From (SELECT d1+d2*10 AS mes FROM 
(SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t1, 
(SELECT 0 AS d2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t2 
LIMIT 12 OFFSET 1 ) AS m
Cross Join otrs.ticket t2
WHERE t2.customer_id = 'Dial') as meses
LEFT JOIN otrs.ticket t on t.customer_id = meses.customer_id
                   and t.type_id = 11
LEFT JOIN otrs.time_accounting ta on ta.ticket_id = t.id 
                             and MONTH(ta.change_time) = meses.mes
                             and ta.change_time BETWEEN '2018-04-01' AND '2018-06-30'
LEFT JOIN otrs.service AS s2 ON t.service_id = s2.id
WHERE meses.mes BETWEEN MONTH('2018-04-01') AND MONTH('2018-06-30')
GROUP BY  meses.mes, meses.customer_id    
ORDER BY meses.mes

That returns me the following content:

Howcouldyouaddupthattimereturned?IfItaketheGroupbybymeses.mesthedisplayedresultofthedifferent,Iendupgettingthis:

mysql sql

asked by anonymous 06.06.2018 / 15:52

1 answer

How to block the sending of package-lock.json by gitignore? MySQL Error: ER_TOO_LONG_KEY

score 2 · Accepted Answer

I do not quite understand why it brings different results by just removing the grouping, but if you turn this into a subquery you should get the correct return:

SELECT customer_id, SUM(TEMPO_CHAMADO) FROM
    (SELECT
    (case when day(sysdate()) < 10 and meses.mes = month(sysdate()) then 0 else
    CAST(SUM(CASE WHEN s2.name LIKE '%Servidores' THEN ( coalesce(ta.time_unit,0) * 1.5)/ 60 ELSE coalesce(ta.time_unit,0)/60 END) AS DECIMAL(12, 3)) end) AS TEMPO_CHAMADO,
    meses.customer_id
    FROM
    (Select distinct
      m.mes,
      t2.customer_id
    From (SELECT d1+d2*10 AS mes FROM 
    (SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t1, 
    (SELECT 0 AS d2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t2 
    LIMIT 12 OFFSET 1 ) AS m
    Cross Join otrs.ticket t2
    WHERE t2.customer_id = 'Dial') as meses
    LEFT JOIN otrs.ticket t on t.customer_id = meses.customer_id
                       and t.type_id = 11
    LEFT JOIN otrs.time_accounting ta on ta.ticket_id = t.id 
                                 and MONTH(ta.change_time) = meses.mes
                                 and ta.change_time BETWEEN '2018-04-01' AND '2018-06-30'
    LEFT JOIN otrs.service AS s2 ON t.service_id = s2.id
    WHERE meses.mes BETWEEN MONTH('2018-04-01') AND MONTH('2018-06-30')
    GROUP BY  meses.mes, meses.customer_id) as tabela_tmp
GROUP BY customer_id