Group and add to a query in SQL Server

Question

Good afternoon,

I have the following problem:

I’m developing a C# program for internal company data query. It is working perfectly, but I need to group field(s) and add another(s) that contains values.

Query (C# + MSSQL):

SqlCommand myCommand = new SqlCommand("SELECT CONVERT(datetime,SF2010.F2_EMISSAO) AS 'DATA DE EMISSÃO', " +
            "SF2010.F2_DOC AS 'NF',SD2010.D2_PEDIDO AS 'PEDIDO TUPAN', SC5010.C5_PEDCLI AS 'PEDIDO CLIENTE', " +
            "SA1010.A1_CGC AS 'CNPJ', SA1010.A1_NOME AS 'CLIENTE', SA1010.A1_MUN AS 'MUNICÍPIO', SF2010.F2_EST AS 'ESTADO'," +
            " SA4010.A4_NOME AS 'TRANSPORTADORA', SF2010.F2_TPFRETE AS 'FRETE', SF2010.F2_VOLUME1 AS 'VOLUME', " +
            "  cast(SF2010.F2_PBRUTO  AS DECIMAL(15,2)) AS 'PESO BRUTO', SD2010.D2_TOTAL AS 'TOTAL DA NOTA R$'" + //ACABA O SELECT, FROM ABAIXO
            "FROM SA4010 " +
            "RIGHT JOIN(SA3010 AS VCAD RIGHT JOIN(SA3010 AS VFAT " +
            "RIGHT JOIN(SC5010 RIGHT JOIN(SB1010 RIGHT JOIN(SD2010 LEFT JOIN(SA1010 RIGHT JOIN SF2010 " +
            "ON(SA1010.A1_LOJA = SF2010.F2_LOJA) AND(SA1010.A1_COD = SF2010.F2_CLIENTE)) ON(SD2010.D2_FILIAL = SF2010.F2_FILIAL) " +
            "AND(SD2010.D2_SERIE = SF2010.F2_SERIE) AND(SD2010.D2_DOC = SF2010.F2_DOC)) ON SB1010.B1_COD = SD2010.D2_COD) " +
            "ON SC5010.C5_NUM = SD2010.D2_PEDIDO) ON VFAT.A3_COD = SC5010.C5_VEND1) ON VCAD.A3_COD = SA1010.A1_VEND) " +
            "ON SA4010.A4_COD = SF2010.F2_TRANSP WHERE(((SD2010.D2_FILIAL) = ([SF2010].[F2_FILIAL])) " +
            "AND((SC5010.C5_FILIAL) = ([SF2010].[F2_FILIAL]))) AND SF2010.F2_EMISSAO >= dateadd(day, -30, getdate()) " +
            "AND SF2010.D_E_L_E_T_ <> '*' AND SD2010.D_E_L_E_T_ <> '*' AND SA1010.D_E_L_E_T_ <> '*' " +
            "AND SC5010.D_E_L_E_T_ <> '*' ORDER BY SF2010.F2_EMISSAO DESC, SF2010.F2_DOC DESC, SF2010.F2_SERIE, " +
            "SA1010.A1_COD, SA1010.A1_LOJA, SD2010.D2_COD;  ", Conexção.ConexaoConfig);

I want the TOTAL OF NOTE R$ be it ADDED when there is repetition country NF and in the end be GROUPED, as shown below:

Example: http://www.java2s.com/Code/SQL/Select-Clause/UseGROUPBYandORDERBYtogether.htm

Answer 1

One way to solve what you need is to build something like

-- código #1
SELECT SF2010.F2_DOC, 
       sum(SD2010.D2_TOTAL) as [TOTAL DA NOTA (R$)],
       max(demais colunas exceto D2_TOTAL) as nome da coluna
  from ...
  group by SF2010.F2_DOC;

The above outline application in the original code would result in something similar to

-- código #3 v2
SqlCommand myCommand = new SqlCommand("SELECT max( CONVERT(datetime,SF2010.F2_EMISSAO) ) AS [DATA DE EMISSÃO], " +
       "SF2010.F2_DOC AS [NF], " +
       "max( SD2010.D2_PEDIDO ) AS [PEDIDO TUPAN], " +
       "max( SC5010.C5_PEDCLI ) AS [PEDIDO CLIENTE], " +
       "max( SA1010.A1_CGC ) AS [CNPJ], " +
       "max( SA1010.A1_NOME ) AS [CLIENTE], " +
       "max( SA1010.A1_MUN ) AS [MUNICÍPIO], " +
       "max( SF2010.F2_EST ) AS [ESTADO]," +
       "max( SA4010.A4_NOME ) AS [TRANSPORTADORA], " +
       "max( SF2010.F2_TPFRETE ) AS [FRETE], " +
       "max( SF2010.F2_VOLUME1 ) AS [VOLUME], " +
       "max( cast(SF2010.F2_PBRUTO AS DECIMAL(15,2)) ) AS [PESO BRUTO], " +
       "sum( SD2010.D2_TOTAL ) AS [TOTAL DA NOTA (R$)]" + //ACABA O SELECT, FROM ABAIXO
  "FROM SA4010 " +
       "RIGHT JOIN(SA3010 AS VCAD RIGHT JOIN(SA3010 AS VFAT " +
       "RIGHT JOIN(SC5010 RIGHT JOIN(SB1010 RIGHT JOIN(SD2010 LEFT JOIN(SA1010 RIGHT JOIN SF2010 " +
       "ON(SA1010.A1_LOJA = SF2010.F2_LOJA) AND(SA1010.A1_COD = SF2010.F2_CLIENTE)) ON(SD2010.D2_FILIAL = SF2010.F2_FILIAL) " +
       "AND(SD2010.D2_SERIE = SF2010.F2_SERIE) AND(SD2010.D2_DOC = SF2010.F2_DOC)) ON SB1010.B1_COD = SD2010.D2_COD) " +
       "ON SC5010.C5_NUM = SD2010.D2_PEDIDO) ON VFAT.A3_COD = SC5010.C5_VEND1) ON VCAD.A3_COD = SA1010.A1_VEND) " +
       "ON SA4010.A4_COD = SF2010.F2_TRANSP WHERE(((SD2010.D2_FILIAL) = ([SF2010].[F2_FILIAL])) " +
       "AND((SC5010.C5_FILIAL) = ([SF2010].[F2_FILIAL]))) AND SF2010.F2_EMISSAO >= dateadd(day, -30, getdate()) " +
       "AND SF2010.D_E_L_E_T_ <> '*' AND SD2010.D_E_L_E_T_ <> '*' AND SA1010.D_E_L_E_T_ <> '*' " +
       "AND SC5010.D_E_L_E_T_ <> '*' " +
  "GROUP BY SF2010.F2_DOC " + 
  "ORDER BY [DATA DE EMISSÃO] DESC, [NF] DESC;", Conexção.ConexaoConfig);

---

However, if there are differences in some of the other columns for the same value of the column SF2010.F2_DOC, it should not be grouped only by this column. Another radical approach would then be something like

-- código #2
SELECT SF2010.F2_DOC, 
       sum(SD2010.D2_TOTAL) as [TOTAL DA NOTA (R$)],
       demais colunas exceto D2_TOTAL
  from ...
  group by SF2010.F2_DOC, demais colunas exceto D2_TOTAL;

The above outline application in the original code would result in something similar to

-- código #4
SqlCommand myCommand = new SqlCommand("SELECT CONVERT(datetime, SF2010.F2_EMISSAO) AS [DATA DE EMISSÃO], " +
       "SF2010.F2_DOC AS [NF], " +
       "SD2010.D2_PEDIDO AS [PEDIDO TUPAN], " +
       "SC5010.C5_PEDCLI AS [PEDIDO CLIENTE], " +
       "SA1010.A1_CGC AS [CNPJ], " +
       "SA1010.A1_NOME AS [CLIENTE], " +
       "SA1010.A1_MUN AS [MUNICÍPIO], " +
       "SF2010.F2_EST AS [ESTADO]," +
       "SA4010.A4_NOME AS [TRANSPORTADORA], " +
       "SF2010.F2_TPFRETE AS [FRETE], " +
       "SF2010.F2_VOLUME1 AS [VOLUME], " +
       "cast(SF2010.F2_PBRUTO AS DECIMAL(15,2)) AS [PESO BRUTO], " +
       "sum( SD2010.D2_TOTAL ) AS [TOTAL DA NOTA (R$)]" + //ACABA O SELECT, FROM ABAIXO
  "FROM SA4010 " +
       "RIGHT JOIN(SA3010 AS VCAD RIGHT JOIN(SA3010 AS VFAT " +
       "RIGHT JOIN(SC5010 RIGHT JOIN(SB1010 RIGHT JOIN(SD2010 LEFT JOIN(SA1010 RIGHT JOIN SF2010 " +
       "ON(SA1010.A1_LOJA = SF2010.F2_LOJA) AND(SA1010.A1_COD = SF2010.F2_CLIENTE)) ON(SD2010.D2_FILIAL = SF2010.F2_FILIAL) " +
       "AND(SD2010.D2_SERIE = SF2010.F2_SERIE) AND(SD2010.D2_DOC = SF2010.F2_DOC)) ON SB1010.B1_COD = SD2010.D2_COD) " +
       "ON SC5010.C5_NUM = SD2010.D2_PEDIDO) ON VFAT.A3_COD = SC5010.C5_VEND1) ON VCAD.A3_COD = SA1010.A1_VEND) " +
       "ON SA4010.A4_COD = SF2010.F2_TRANSP WHERE(((SD2010.D2_FILIAL) = ([SF2010].[F2_FILIAL])) " +
       "AND((SC5010.C5_FILIAL) = ([SF2010].[F2_FILIAL]))) AND SF2010.F2_EMISSAO >= dateadd(day, -30, getdate()) " +
       "AND SF2010.D_E_L_E_T_ <> '*' AND SD2010.D_E_L_E_T_ <> '*' AND SA1010.D_E_L_E_T_ <> '*' " +
       "AND SC5010.D_E_L_E_T_ <> '*' " +
  "GROUP BY SF2010.F2_EMISSAO, SF2010.F2_DOC, SD2010.D2_PEDIDO, SC5010.C5_PEDCLI, SA1010.A1_CGC, " + 
       "SA1010.A1_NOME, SA1010.A1_MUN, SF2010.F2_EST, SA4010.A4_NOME, SF2010.F2_TPFRETE, " +
       "SF2010.F2_VOLUME1, SF2010.F2_PBRUTO " +
  "ORDER BY [DATA DE EMISSÃO] DESC, [NF] DESC;", Conexção.ConexaoConfig);

---

I suggest you review the construction of the FROM clause, because in T-SQL the pairs of "(" and ")" have no effect on defining the sequence in which the tables are evaluated for execution.