Group by month (in each column) SQL

Asked

Viewed 44 times

0

I am using the code below to make the column of the month of January, but I could not find a way to return the month of February to the side.

SELECT 
    CASE 
    WHEN CODGRUPOPROD LIKE '20%' 
    THEN 'TALHA CORRENTE'
    WHEN CODGRUPOPROD LIKE '21%' 
    THEN 'TALHA CABO DE AÇO'
    WHEN CODGRUPOPROD LIKE '22%' 
    THEN 'PONTE ROLANTE'
    WHEN CODGRUPOPROD LIKE '23%'
    THEN 'SISTEMA LCS'
    WHEN CODGRUPOPROD LIKE '25%' 
    THEN 'MONOVIA'
    WHEN CODGRUPOPROD LIKE '27%'
    THEN 'GUINDASTE'
    ELSE 'OUTROS'
    END  AS 'EQUIPAMENTO',

    SUM(VLRUNIT * ITE.QTDNEG) AS JANEIRO

FROM

TGFCAB CAB
INNER JOIN TGFITE ITE ON CAB.NUNOTA = ITE.NUNOTA
INNER JOIN TGFPRO PRO ON ITE.CODPROD = PRO.CODPROD

WHERE 
CAB.AD_LIBERACAO_INTERNA BETWEEN '01/01/2021' AND '31/01/2021' AND CODTIPOPER IN (39,87,3100) AND AD_PEDIDO_CANCELADO IS NULL

GROUP BY 
    CASE 
    WHEN CODGRUPOPROD LIKE '20%' 
    THEN 'TALHA CORRENTE'
    WHEN CODGRUPOPROD LIKE '21%' 
    THEN 'TALHA CABO DE AÇO'
    WHEN CODGRUPOPROD LIKE '22%' 
    THEN 'PONTE ROLANTE'
    WHEN CODGRUPOPROD LIKE '23%'
    THEN 'SISTEMA LCS'
    WHEN CODGRUPOPROD LIKE '25%' 
    THEN 'MONOVIA'
    WHEN CODGRUPOPROD LIKE '27%'
    THEN 'GUINDASTE'
    ELSE 'OUTROS'
    END

THE RESULT IS THE RESULT OF THE IMAGE BELOW

inserir a descrição da imagem aqui

mysql sql sql-server
  • 1

    I guess I could group just by code without the case: GROUP BY CODGRUPOPROD. Over the month, you also need to group to bring separate values, for example add to select and group by YEAR(CAB.AD_LIBERACAO_INTERNA), MONTH(CAB.AD_LIBERACAO_INTERNA)

  • About the case in codgrupoprod is the question of the description of the group, now about the month, it would have to group the value , within that month in the" column" , when I use Month it group by row.

  • to help more need to have an example of the data... can add in question or mount an example of sqlfiddle.com for example?

1 answer

0

This should work, CASE per month, maybe a Function mysql or sql-server (which BD is ?) type MONTH , EXTRACT gets better

SELECT 
    CASE 
    WHEN CODGRUPOPROD LIKE '20%' 
    THEN 'TALHA CORRENTE'
    WHEN CODGRUPOPROD LIKE '21%' 
    THEN 'TALHA CABO DE AÇO'
    WHEN CODGRUPOPROD LIKE '22%' 
    THEN 'PONTE ROLANTE'
    WHEN CODGRUPOPROD LIKE '23%'
    THEN 'SISTEMA LCS'
    WHEN CODGRUPOPROD LIKE '25%' 
    THEN 'MONOVIA'
    WHEN CODGRUPOPROD LIKE '27%'
    THEN 'GUINDASTE'
    ELSE 'OUTROS'
    END  AS 'EQUIPAMENTO',

    SUM(CAB.AD_LIBERACAO_INTERNA BETWEEN '01/01/2021' AND '31/01/2021' THEN (VLRUNIT * ITE.QTDNEG) ELSE 0 END) JANEIRO,
    SUM(CAB.AD_LIBERACAO_INTERNA BETWEEN '01/02/2021' AND '28/02/2021' THEN (VLRUNIT * ITE.QTDNEG) ELSE 0 END) FEVEREIRO,
    /*REPETE AS LINHAS PARA OS MESES*/
 

FROM

TGFCAB CAB
INNER JOIN TGFITE ITE ON CAB.NUNOTA = ITE.NUNOTA
INNER JOIN TGFPRO PRO ON ITE.CODPROD = PRO.CODPROD

WHERE 
CAB.AD_LIBERACAO_INTERNA BETWEEN '01/01/2021' AND '31/12/2021' AND CODTIPOPER IN (39,87,3100) AND AD_PEDIDO_CANCELADO IS NULL

GROUP BY 
    CASE 
    WHEN CODGRUPOPROD LIKE '20%' 
    THEN 'TALHA CORRENTE'
    WHEN CODGRUPOPROD LIKE '21%' 
    THEN 'TALHA CABO DE AÇO'
    WHEN CODGRUPOPROD LIKE '22%' 
    THEN 'PONTE ROLANTE'
    WHEN CODGRUPOPROD LIKE '23%'
    THEN 'SISTEMA LCS'
    WHEN CODGRUPOPROD LIKE '25%' 
    THEN 'MONOVIA'
    WHEN CODGRUPOPROD LIKE '27%'
    THEN 'GUINDASTE'
    ELSE 'OUTROS'
    END

Browser other questions tagged mysql sql sql-server

You are not signed in. Login or sign up in order to post.