CODEWITHSUNDEEP
sql-serverprocedure
Alex Castillo
Alex Castillo

Reputation: 37

SQL Server sum all the rows with the same row name

I am doing a project and I am stuck with this stored procedure, I am using SQL Server 2014:

ALTER PROC FOF_FerreteriaMasGanancia
AS
BEGIN
    SELECT 
        F.Nombre, (P.Precio * CA.Cantidad)  as 'Gananacia' 
    FROM
        FO_Carrito CA
    JOIN
        FO_Solicitud S ON S.ID = CA.FK_SolicitudC
    JOIN 
        FO_Recibo R ON R.FK_Solicitud = S.ID
    JOIN 
        FO_Productos P ON P.ID = CA.FK_ProductosC 
    JOIN 
        FO_Cliente C ON C.ID = S.FK_Cliente
    JOIN 
        FO_Estante E ON E.FK_Producto = P.ID
    JOIN 
        FO_PasilloXDepartamento PD ON PD.FK_Estante = E.NumeroEstante 
    JOIN 
        dbo.FO_Departamento D ON D.ID = PD.FK_Departamento
    JOIN 
        dbo.FO_Ferreteria F ON D.FK_Ferreteria = F.ID
    GROUP BY
        F.Nombre, (P.Precio * CA.Cantidad) 
    ORDER BY 
        (P.Precio * CA.Cantidad) DESC
END

I am getting this:

Ferreteria2 12
Ferreteria2 10
Ferreteria5 8
Ferreteria5 6
Ferreteria2 5
Ferreteria5 4
Ferreteria1 3
Ferreteria4 2
Ferreteria1 1

How can I get this instead?

Ferreteria2 27
Ferreteria5 18
Ferreteria1 4
Ferreteria4 2

Upvotes: 0

Views: 66

Answers (1)

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

I have assumed you are doing sum on P.PRECIO * CA.CANTIDAD. So i have modified like below.

 ALTER PROC Fof_ferreteriamasganancia
    AS
      BEGIN
          SELECT F.NOMBRE,
                 Sum(P.PRECIO * CA.CANTIDAD) AS 'GANANACIA'
          FROM   FO_CARRITO CA
                 JOIN FO_SOLICITUD S
                   ON S.ID = CA.FK_SOLICITUDC
                 JOIN FO_RECIBO R
                   ON R.FK_SOLICITUD = S.ID
                 JOIN FO_PRODUCTOS P
                   ON P.ID = CA.FK_PRODUCTOSC
                 JOIN FO_CLIENTE C
                   ON C.ID = S.FK_CLIENTE
                 JOIN FO_ESTANTE E
                   ON E.FK_PRODUCTO = P.ID
                 JOIN FO_PASILLOXDEPARTAMENTO PD
                   ON PD.FK_ESTANTE = E.NUMEROESTANTE
                 JOIN DBO.FO_DEPARTAMENTO D
                   ON D.ID = PD.FK_DEPARTAMENTO
                 JOIN DBO.FO_FERRETERIA F
                   ON D.FK_FERRETERIA = F.ID
          GROUP  BY F.NOMBRE
          ORDER  BY 2 DESC
      END

Upvotes: 3

Related Questions