Sum same column with different conditions then Group By-CodePudding

I'm trying to sum the same column but with different conditions and the group then all by "Cliente" (ID)

When I use

SELECT Cliente, SUM([Valor Original]) As Emitidos FROM TAB_Documentos WHERE Condição = 0 GROUP BY Cliente

I get this result, but I need to join/union with "Baixados", "Devolvidos", "Outros" and then group them all by "Cliente"...

I'm trying to figure out how to join that SQL string... Is basicly what I need, but I need them all together...

SELECT Cliente, SUM([Valor Original]) As Emitidos FROM TAB_Documentos WHERE Condição = 0 GROUP BY Cliente
SELECT Cliente, SUM([Valor Original]) As Baixados FROM TAB_Documentos WHERE Condição = 1 GROUP BY Cliente
SELECT Cliente, SUM([Valor Original]) As Devolvidos FROM TAB_Documentos WHERE Condição = 2 GROUP BY Cliente
SELECT Cliente, SUM([Valor Original]) As OutrosFROM TAB_Documentos WHERE Condição = 3 GROUP BY Cliente

CodePudding user response：

Put the filters inside the aggregate function, instead of using a WHERE clause...

(This is often termed as conditional aggregation.)

SELECT
  Cliente,
  SUM(CASE WHEN Condição = 0 THEN [Valor Original] END) As Emitidos,
  SUM(CASE WHEN Condição = 1 THEN [Valor Original] END) As Baixados,
  SUM(CASE WHEN Condição = 2 THEN [Valor Original] END) As Devolvidos,
  SUM(CASE WHEN Condição = 3 THEN [Valor Original] END) As Outros
FROM
  TAB_Documentos
GROUP BY
  Cliente

However, SQL is often much better suited to normalised data. For example, the simplest query is actually...

SELECT
  Cliente,
  Condição,
  SUM([Valor Original]) AS [Valor]
FROM
  TAB_Documentos
GROUP BY
  Cliente,
  Condição

While this isn't the format you asked for, it's shorter and easier to reuse in subsequent SQL; it's the way SQL is designed to be written.