Select and sum qty from two tables (mysql)

Question

I would like to sum something like this: My first table shows only projects:

  id  |progress [%]  | 
  1   |100           | 
  2   |5             |
  3   |5             | 
  4   |100           | 
  5   |10            | 

Second table shows tasks where project_id has the same numbers id in the first table (id = Project_id):

  Project_id |status     | 
  1          |done       | 
  2          |done       |
  3          |undone     | 
  4          |in_progress| 
  5          |done       | 

So I would like to join these two tables and get one row result:

| done   | undone | in_progress |
| 2      |  1     | 0           |

I would like to sum all the tasks (second table) with their statuses but without those tasks which are inside projects (first table) with 100% progress.

Answer 1

Use SUM with CASE statements.

SELECT SUM(CASE WHEN status = 'done' THEN 1 ELSE 0 END) done,
SUM(CASE WHEN status = 'undone' THEN 1 ELSE 0 END) undone, 
SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) in_progress
FROM yourtablea a
INNER JOIN yourtableb b ON a.id = b.Project_id
WHERE progress != '100'

Output

done undone in_progress
2    1      0

SQL Fiddle: http://sqlfiddle.com/#!9/6b936/2/0

Answer 2

select SUM(if(a.status ="done", 1,0)) as `Done`, SUM(if(a.status ="undone", 1,0)) as `UnDone`, SUM(if(a.status ="in_progress", 1,0)) as `UnDone` from Status_table a join Progress_table b on a.Project_id = b.id  WHERE b.progress != '100';