sql – How to perform an arithmetic operation without ordering it in GROUP BY

Question:

Having the following SELECT querying a pivot table from a subselect, I get the query result correctly but with records divided in the following example:

cod_representante | ... | VALOR_TOTAL_MERCEARIA | VALOR_TOTAL_PC_MP
 123              | ... | 59000                 | 0
 123              | ... | 0                     | 2000 

When should it be grouped only by the cod_representante :

cod_representante | ... | VALOR_TOTAL_MERCEARIA | VALOR_TOTAL_PC_MP
123             | ... | 59000                 | 2000

O Select:

SELECT codFunc,
       regional AS gerente,
       nome_gerente AS supervisor,
       cod_representante,
       nome_representante,
       SUM(valor_total_seca) AS VALOR_TOTAL_MERCEARIA,
       SUM(valor_total_liquida) AS VALOR_TOTAL_PC_MP,
       
       ISNULL(valor_total_seca,0) + ISNULL(valor_total_liquida,0) AS TOTAL_GERAL,
       ISNULL(valor_rateio,0) AS VALOR_DAS_DEVOLUCOES,
       CASE
           WHEN valor_rateio IS NULL THEN (ISNULL(valor_total_seca,0) + ISNULL(valor_total_liquida,0))
           ELSE (ISNULL(valor_total_seca,0) + ISNULL(valor_total_liquida,0)) - ISNULL(valor_rateio,0)
       END AS TOTAL_SEM_DEV,
       ISNULL(comissao, 0) AS PERCENTUAL_COMISSAO,
       ((ISNULL(valor_total_seca,0) + ISNULL(valor_total_liquida,0)) - ISNULL(valor_rateio,0)) * ISNULL(comissao,0) AS VALOR_DA_COMISSAO
FROM ...

O GROUP BY:

GROUP BY regional,
         cod_gerente,
         nome_gerente,
         cod_representante,
         codFunc,
         nome_representante,
         valor_total_seca ,
         valor_total_liquida,
         valor_rateio,
         comissao

If I don't include the fields that are being manipulated in the arithmetic operation, in the excerpt below,

ISNULL(valor_total_seca,0) + ISNULL(valor_total_liquida,0) AS TOTAL_GERAL,
           ISNULL(valor_rateio,0) AS VALOR_DAS_DEVOLUCOES,
           CASE
               WHEN valor_rateio IS NULL THEN (ISNULL(valor_total_seca,0) + ISNULL(valor_total_liquida,0))
               ELSE (ISNULL(valor_total_seca,0) + ISNULL(valor_total_liquida,0)) - ISNULL(valor_rateio,0)
           END AS TOTAL_SEM_DEV,
           ISNULL(comissao, 0) AS PERCENTUAL_COMISSAO,
           ((ISNULL(valor_total_seca,0) + ISNULL(valor_total_liquida,0)) - ISNULL(valor_rateio,0)) * ISNULL(comissao,0) AS VALOR_DA_COMISSAO

I get the following error:

Msg 8120, Level 16, State 1, Line 13 Column 't.valor_total_seca' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Msg 8120, Level 16, State 1, Line 13 Column 't.valor_total_liquida' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer:

You can select out of everything with the fields grouped together. Without seeing the entire SLQ I won't be able to guarantee 100%, but this would be a solution:

SELECT cod_representante ,
       count(VALOR_TOTAL_MERCEARIA),
       count(VALOR_TOTAL_PC_MP)
FROM (
  --todo o seu sql atual
)
gropy by cod_representante 

This new SQL will run really fast because it will only get the records returned from your current one.

UPDATING

I even deleted the answer because I didn't like my solution, but looking at the problem a second time I think this is happening because you use these columns in two columns in SQL, I think a solution like the one I gave would make it easier to read of SQL.

Scroll to Top