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

Question

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

Navigation

#1 by (2 votes)

3

Having the following SELECT querying a PivotTable from a subselect, I get the result of the query correctly however with records divided into the following example:

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

When should you only group it with 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 ...

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 that are 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.value_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.value_total_liquid' is invalid in the select   list because it is not contained in either an aggregate function or   the GROUP BY clause.

sql sql-server

asked by anonymous 01.12.2015 / 18:35

1 answer

Robot to perform web procedures similar to PhantomJS Read RTF files and generate a TXT file

score 2 · Accepted Answer

You can make a select out of everything with the clustered fields. Without seeing the whole SLQ I will not 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 one.

UPDATING

I got to delete the answer because I did not like my solution, but looking a second time the problem I think this is happening because you use these columns in two columns in SQL, I think a solution like this that I gave up would make it easier to read SQL.