Home / Questions / 553391
Accepted
Efrain Mejias C
Asked: 2022-08-24 15:45:01 +0800 CST 2022-08-24 15:45:01 +0800 CST

How to sum rows and columns in SqlServer query?

  • 772

I have the following query and it returns the fields as in the photo below.

SELECT A.Cuenta_Id, B.EstructuraCuenta_Id, B.Cuenta, B.Descripcion, C.Periodo_Id, D.Descripcion , C.Debito, C.Credito FROM Cuenta A 
INNER JOIN EstructuraCuenta B ON A.EstructuraCuenta_Id = B.EstructuraCuenta_Id
INNER JOIN  SaldoCuenta C  ON A.Cuenta_Id = C.Cuenta_Id  
INNER JOIN  Periodo D ON  C.Periodo_Id = D.Periodo_Id WHERE C.Periodo_id >=1  AND c.Periodo_Id <= 5
GROUP BY A.Cuenta_Id, B.EstructuraCuenta_Id, B.Cuenta, B.Descripcion, C.Periodo_Id, C.Debito, C.Credito , D.Descripcion

enter image description here

For example, if I take the Account = 11100501, it would give me the sum of the Debit column and the sum of the Credit column corresponding to this account. and the subtraction of these sums.

Cuenta          Descripcion       Debito          Credito            (Debito – Credito )
11100501    COL CTE 04054484786  584.268.251.222  531.209.177.128    -53.059.074.094
sql

1 Answers

  1. Best Answer

    You don't seem to know what it means GROUP BY. GROUP BY x, y, zit says "I want a row for x,y,z in the result". You want one row per count, so you want GROUP BY Cuenta_Id.

    If you use GROUP BY Cuenta_Id, Debito, Credito, you keep the original rows (with the exception that if there are two rows with the same Debit and Credit for an account, you receive a single row instead of the two with duplicate Debit and Credit).

    Well, you want to show one row per account and also show information about its structure. But the ID and description of your period? What period are you talking about? An account can have more than one period. You have to decide which period you want to show. The minimum or the maximum perhaps? You may not want to show no period at all ;-)

    Well, now that you have the explanation, it should be mentioned that SQL Server does not allow a solo GROUP BY Cuenta_Idif you want to show its structure. This seems weird, because you show one row per account, and an account has only one structure. What is SQL Server problem in this situation? The matter is called "functional dependency." The structure depends on the account. But SQL Server is not able to detect this dependency and thus does not respect this SQL Standard rule. You would get a message like "Msg 8120 Level 16 State 1 Line 1 Column 'B.Descripcion' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause".

    So you have to circumvent this weakness of SQL Server. One way is to have these columns in the GROUP BY(eg GROUP BY Cuenta_Id, B.Descripcion), another is to apply an inoperable aggregation function to these columns (eg MIN(B.Descripcion)). And there is one more option, and this one I prefer. Instead of joining all balances, to add the data after, add the balances before join, to join the summed balances .

    Finally, of course you need the SUMmissing function in your query completely ;-)

    The query I recommend:

    SELECT
  c.cuenta_id,
  ec.estructuracuenta_id,
  ec.cuenta,
  ec.descripcion,
  sc.debito_total,
  sc.credito_total
FROM cuenta c
INNER JOIN estructuracuenta ec on ec.estructuracuenta_id = c.estructuracuenta_id
INNER JOIN
(
  SELECT cuenta_id, SUM(debito) AS debito_total, SUM(credito) AS credito_total
  FROM saldocuenta
  WHERE periodo_id BETWEEN 1 AND 5
  GROUP BY cuenta_id
) sc ON sc.cuenta_id = s.cuenta_id 
ORDER BY c.cuenta_id;
    • 2