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

Question

Having the following SELECT consulting a dynamic table from a subselect, I get the result of the query 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 I group it only by 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 ...

THE 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 do not include the fields being manipulated in the arithmetic operation, in the section 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 1

You can make a select out of everything with the fields grouped. 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 very fast because it will only get the records returned from your current.

UPDATING

I came to delete the answer because I didn’t like my solution, but looking a second time the problem I think is happening because you use these columns in two columns in SQL, I think a solution like this I gave would even facilitate the reading of SQL.