SQL SERVER. Add the amount of all records

Question

SQL SERVER. Add the amount of all records

Navigation

#1 by (3 votes)
#2 by (1 votes)

1

I have an example problem about bank transactions. My table contains a lot of data such as Cash, Office, Name, Last Name, Date, ID, Amount, Type ...

Each box (there are several) has several offices, and these in turn have many customers. After a few previous consultations I have reached the next, so I have to leave yes or yes of it:

      SELECT Caja, Oficina, MIN(NombreCompleto) AS Persona
      FROM (
            SELECT Caja, Oficina, DNI, MIN(Nombre+Apellidos) AS NombreCompleto, Importe
            FROM miTabla
            WHERE Importe > 50
            GROUP BY Caja, Oficina, DNI, Importe) as tabla1
      GROUP BY Caja, Oficina
      ORDER BY Caja, Oficina, Nombre

This shows me the first person in alphabetical order (eg Alberto Álvarez) inside all the offices, inside each box. That is, one person per office.

So now what I need is for a 4th field to appear (apart from cash, office, person, which already shows me the previous query) that is the sum of all the amounts associated with those people that already appear to me.

That is to say, if 5 people appear to me, I want to add the amount that each of them has and that the result appears and repeats itself throughout the entire output.

I am trying to put the SUM (Amount) in several places but it does not work out. I hope you understand me.

Thank you.

sql sql-server

asked by Miguel 01.02.2018 в 16:27

source

2 answers

Store the original name of an image that was sent in a form Problems when storing strings of a class in an array

score 3 · Answer 1

Ready, your question is better explained, the query below serves you:

SELECT S3.Caja, s3.Oficina, S3.DNI, S3.Persona, SUM(S3.Importe)
FROM(
    SELECT S1.Caja, S1.Oficina, S1.DNI, S1.Persona, S2.Importe
    FROM(
        SELECT Caja, Oficina, DNI, MIN(IsNUll(Nombre, '') + ' ' + IsNull(Apellido, '')) As Persona
        FROM miTabla AS A
        WHERE IMPORTE > 50
        GROUP BY Caja, Oficina, DNI) AS S1,
        (SELECT Caja, Oficina, DNI, MIN(IsNUll(Nombre, '') + ' ' + IsNull(Apellido, ''), Importe) As Persona
        FROM miTabla AS A
        WHERE IMPORTE > 50) AS S2
    WHERE S1.Persona = S2.Persona) AS S3
GROUP BY S3.Caja, s3.Oficina, S3.DNI, S3.Persona

In which:

S1, it's the first people alphabetically sorted.
S2, is the same table with all the people and their amounts
S3, is the query that links you to the people of the set S1 and S2 by name, and thus you get what you want by grouping.

I hope it serves you. Greetings.

score 1 · Answer 2

I would do it this way.

  ;WITH persona AS
    (
        SELECT Caja, Oficina, DNI, MIN(Nombre+Apellidos) AS NombreCompleto
        FROM miTabla
        WHERE Importe > 50
        GROUP BY Caja, Oficina, DNI
    )

    SELECT  persona.Caja, persona.Oficina, persona.DNI, persona.NombreCompleto, SUM(Importe)
    FROM persona
    JOIN miTabla ON
        miTabla.Caja    = persona.Caja    AND
        miTabla.Oficina = persona.Oficina AND
        miTabla.DNI     = persona.DNI
    GROUP BY persona.Caja, persona.Oficina, persona.DNI, persona.NombreCompleto