SUM with inner join mysql

Navigation
2

I want to show information on each provider to make a sum of how much income he had on a given date. 

Mi tabla entrada
-----------------------
cajas  peso idproveedor
-----------------------
100    1000      1
100    2000      1


Tabla proveedor
-----------------------
id  nombre
-----------------------
1   Manuel Morales

I was doing it this way: 

SELECT
COALESCE(SUM(cajas),0) as total_cajas, 
COALESCE(SUM(peso),0) as total_peso
FROM entrada INNER JOIN entrada.idproveedor = proveedor.id

but it marks me wrong. How could I make the next result come out? 

---------------------------------------------------------
idproveedor   nombre_proveedor  total_cajas   total_peso
---------------------------------------------------------
    1         Manuel Morales     200           3000

and so on with each provider

    
asked by DoubleM 05.05.2018 в 01:30
source

2 answers

1

You can do it like this: 

SELECT proveedor.id, proveedor.nombre,
COALESCE(SUM(cajas),0) as total_cajas, 
COALESCE(SUM(peso),0) as total_peso
FROM entrada INNER JOIN proveedor ON entrada.idproveedor = proveedor.id
GROUP BY entrada.idproveedor
    
answered by 05.05.2018 / 01:34
source
0

Hi, I think the advisable thing in this case is to use Group By (MySQL). 

CREATE TABLE PROVEEDOR(
  ID int NOT NULL,
  NOMBRE VARCHAR(255) NOT NULL,
  PRIMARY KEY (ID)
);
CREATE TABLE ENTRADA(
  ID int NOT NULL,
  CAJAS INT,
  PESO INT,
  ID_PROVEEDOR INT,
  PRIMARY KEY (ID),
  FOREIGN KEY (ID_PROVEEDOR) REFERENCES PROVEEDOR(ID)
);

INSERT INTO PROVEEDOR(ID,NOMBRE) VALUES(1,'Manuel Morales');
INSERT INTO ENTRADA(ID,CAJAS,PESO,ID_PROVEEDOR) VALUES(1,100,1000,1);
INSERT INTO ENTRADA(ID,CAJAS,PESO,ID_PROVEEDOR) VALUES(2,100,2000,1);

SELECT P.ID AS ID_PROVEEDOR, 
  P.NOMBRE AS NOMBRE_PROVEEDOR, 
  SUM(E.CAJAS) AS TOTAL_CAJAS,
  SUM(E.PESO) AS TOTAL_PESO
    FROM ENTRADA AS E
    INNER JOIN PROVEEDOR AS P
    ON E.ID_PROVEEDOR = P.ID
    GROUP BY(P.ID)

I leave the SQLFiddle link so you can play with the query link

Greetings.

    
answered by 05.05.2018 в 02:01
Reverse order of Multidimensional Array .append from Jquery loses me the lines and leaves everything followed