Sum of cost in MySQL

user_13419995 0
Viewed 48

I have two tables, for example 1st has id, and name. 2nd has id, link to 1st table by id and COST.

CREATE TABLE FIRST_TABLE (id INT PRIMARY KEY AUTO_INCREMENT,
                         name VARCHAR (100));
CREATE TABLE SECOND_TABLE (id INT PRIMARY KEY,  FIRST_TABLE_ID INT NOT NULL, cost DECIMAL(10,2),
                  FOREIGN KEY (FIRST_TABLE_ID) REFERENCES  FIRST_TABLE (ID));


INSERT INTO FIRST_TABLE (NAME) VALUES
  ('ONE'),
  ('TWO'),
  ('THREE');

INSERT INTO SECOND_TABLE (ID, FIRST_TABLE_ID, COST) VALUES
  (1, 1, 500),
  (2, 2, 400),
  (3, 3, 150),
  (4, 1, 500),
  (5, 2, 400),
  (6, 3, 150);

How to get sum of elements COST (of 2nd table), which depends on NAME (of 1st table)?

What i tried to do:

select FIRST_TABLE.NAME, sum(SECOND_TABLE.COST) TOTAL_COST
from FIRST_TABLE
left join SECOND_TABLE on FIRST_TABLE_ID = SECOND_TABLE.ID
group by FIRST_TABLE.ID

The problem is: I have only irregular sum of cost - 1050 for every NAME. ONE - 1050 TWO - 1050 THREE - 1050

How to get genuine values for every NAME?

And how will it look like if i have three tables and for key in 1st i have to get sum of 2nd table and 3rd table?

aggregate-functions left-join sum mysql sql
1 Answers

Here:

from FIRST_TABLE
left join SECOND_TABLE on FIRST_TABLE_ID = SECOND_TABLE.ID

The join condition is actually equivalent to:

on SECOND_TABLE.FIRST_TABLE_ID = SECOND_TABLE.ID

Both operands of the equality relate to the same table. This is not what you want. Instead, use:

select FIRST_TABLE.NAME, sum(SECOND_TABLE.COST) TOTAL_COST
from FIRST_TABLE
left join SECOND_TABLE on SECOND_TABLE.FIRST_TABLE_ID = FIRST_TABLE.ID
group by FIRST_TABLE.ID

I would also recommend using table aliases to shorten the query and make it more readable:

select t1.NAME, sum(t2.COST) TOTAL_COST
from FIRST_TABLE t1
left join SECOND_TABLE t2 on t2.FIRST_TABLE_ID = t1.ID
group by t1.ID
user_106767160
Related