MySQL Forums
Forum List  »  Portuguese

Re: Group By Datas
Posted by: Adriano Rogeri Soares
Date: June 21, 2020 11:58AM

Consegui chegar a uma solução.

PIVOT

select id_profissional, SUM(JAN) ,SUM(FEV) ,SUM(MAR) ,SUM(ABR) ,SUM(MAI),SUM(JUN),SUM(JUL) ,SUM(AGO) ,SUM(SETE) ,SUM(OUTU) ,SUM(NOV),SUM(DEZ) FROM(
SELECT id_profissional,
IF(EXTRACT(MONTH FROM data) = 1, SUM(VALOR_SERVICO), 0) AS JAN,
IF(EXTRACT(MONTH FROM data) = 2, SUM(VALOR_SERVICO),0) AS FEV,
IF(EXTRACT(MONTH FROM data) = 3, SUM(VALOR_SERVICO),0) AS MAR,
IF(EXTRACT(MONTH FROM data) = 4, SUM(VALOR_SERVICO),0) AS ABR,
IF(EXTRACT(MONTH FROM data) = 5, SUM(VALOR_SERVICO),0) AS MAI,
IF(EXTRACT(MONTH FROM data) = 6, SUM(VALOR_SERVICO),0) AS JUN ,
IF(EXTRACT(MONTH FROM data) = 7, SUM(VALOR_SERVICO), 0) AS JUL,
IF(EXTRACT(MONTH FROM data) = 8, SUM(VALOR_SERVICO),0) AS AGO,
IF(EXTRACT(MONTH FROM data) = 9, SUM(VALOR_SERVICO),0) AS SETE,
IF(EXTRACT(MONTH FROM data) = 10, SUM(VALOR_SERVICO),0) AS OUTU,
IF(EXTRACT(MONTH FROM data) = 11, SUM(VALOR_SERVICO),0) AS NOV,
IF(EXTRACT(MONTH FROM data) = 12, SUM(VALOR_SERVICO),0) AS DEZ

from agenda where
id_servico in(select id from servicos where id_job= '1') and
valor_servico>0 and date_format(data,'%Y')='2020' GROUP BY ID_PROFISSIONAL,date_format(DATA,'%m'))T1
GROUP BY id_profissional

Options: ReplyQuote


Subject
Views
Written By
Posted
151
June 20, 2020 09:56PM
Re: Group By Datas
70
June 21, 2020 11:58AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.