Add total of several rows that have the same primary key from another table

Question

Add total of several rows that have the same primary key from another table

Navigation

#1 by (0 votes)

0

Good morning, dear, I am looking for a bit of your help and knowledge regarding an SQL query, since I am giving my first tanes in this query language.

I tell you, I have two tables that store invoice data, in one the header is stored, that is ID of the invoice the Branch , Name of the customer and the invoice total etc, and in the other table the detail is stored, that is to say the products that were invoiced, means that each line of the detail has as a foreign key the ID of the invoice table.

Basically I have to group all the invoices by branch, count how many invoices were issued, how much they all add, add the details to match the total sum of all the invoices.

I appreciate your help, I just need some guidance on this, since I have spent several hours trying and nothing.

Very grateful

Example:

I do so my query, I can not find where my error may be.

SELECT SUCURSAL,COUNT(A.ID_FACTURA),SUM(A.DOC_TOTAL),SUM(B.UNIT_PRICE)  
    FROM FACTURAS_ENCABEZADO AS A
        INNER JOIN DETALLE_FACTURAS AS B ON A.ID_FACTURA=B.ID_FACTURA
GROUP BY (A.SUCURSAL)

sql-server

asked by anibaltsz 17.04.2018 в 03:14

source

1 answer

Complexity time of a circular array Filter by one or more fields

score 0 · Accepted Answer

Primero están mal los títulos de tus tablas de acuerdo a tu imagen, después estas comparando muchos valores iguales por lo que te compara todo lo que sea igual por sucursal con la tabla de 'FACTURA_DETALLE' por lo que genero un subquery para dejar único 'SUCURSAL' e 'ID_FACTURA' y después si lo comparo por SUCURSAL y por ID_FACTURA

  SELECT a.SUCURSAL,COUNT(A.ID_FACTURA) AS ID_FACTURA,SUM(A.DOC_TOTAL) AS DOC_TOTAL,SUM(B.UNIT_PRICE) AS UNIT_PRICE 
    FROM FACTURA_ENCABEZADO AS A
            INNER JOIN (SELECT id_Factura,sucursal,sum(Unit_Price) AS Unit_Price FROM Factura_Detalle group by Id_Factura,Sucursal)  B ON A.ID_FACTURA=B.ID_FACTURA and A.Sucursal=b.Sucursal
    GROUP BY (A.SUCURSAL)