MYSQL - sum values of rows from one table based on criteria from another-CodePudding

I am trying to find out the correct SQL query to carry out the following:

I have 2 tables and want to count total sum values of rows from one of them based on criteria selected from the other.

table1
| id  | colour      | item
| ------------------| ----------|
| 1   | blue        | anorak
| 2   | blue        | jeans
| 3   | green       | t-shirt
| 4   | yellow      | t-shirt


table2
| id  | cost     |
| ---------------|
| 1   | 58       |
| 2   | 22       |
| 3   | 36       |
| 4   | 19       |

So for example how do I find out total cost of all blue items? (Should amount to 80)

If it were all in one table I could do: SELECT SUM(cost) FROM table1 WHERE colour='blue';

How would I do this with the 2 tables?

thanks in advance

CodePudding user response：

it is the same, you need to join the tables

SELECT SUM(cost) FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id WHERE colour='blue';

| SUM(cost) |
| --------: |
|        80 |

db<>fiddle here

CodePudding user response：

you can use where clause and IN operator to find sum of cost of specific product on table which has id exist on the table 2 and colour is blue. simply, blue colors product ids should be find in query 1 with :

SELECT table1.id from table1 where table1.colour = "blue";

sum of cost find with :

 select sum(cost) from table2;

Join with in keyword :

select sum(cost) from table2 where table2.id in (SELECT table1.id from table1 where table1.colour = "blue")

in operator works like a loop and match founded id in where clause query.

CodePudding user response：

Perform a JOIN on the id column to connect the two tables. Then SUM items based on color.

-- Find the total cost where item colour is blue.
SELECT SUM(t2.cost) AS TotalCost
FROM Table2 t2
JOIN Table1 t1 ON t2.id = t1.id
WHERE t1.colour = 'blue';

Note: I used table aliases t1 and t2 for clarity. They are not strictly needed when column names do not overlap/conflict.

If item count is needed, include the MySQL function COUNT() to find the number of matching rows in the set.

-- Find the number of items & total cost where item colour is blue.
SELECT 
  COUNT(*) AS BlueItemCount,
  SUM(t2.cost) AS CostOfBlueItems
FROM Table2 t2
JOIN Table1 t1 ON t2.id = t1.id
WHERE t1.colour = 'blue';