Alex Castillo Alex Castillo - 4 days ago 5
SQL Question

Sql Server sum all the rows with the same row name

I am doing a project and i am stuck with this store procedure, I am using sql server 2014:

GO
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 obtaining this:

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


How can i obtain this?:

Ferreteria2 27
Ferreteria5 18
Ferreteria1 4
Ferreteria4 2

Answer

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 
Comments