Issue
I have two tables, imports and importrows. importrows contains all of the information regarding each entry in an import. I’m assuming this is possible through a subquery.
importrows
import_id | status
0001 | success
0001 | error
0001 | success
0001 | error
0002 | success
0003 | success
0001 | success
0001 | success
0001 | error
0003 | error
imports
import_id | created
0001 | 2015-01-21 10:39:53
0002 | 2015-01-21 10:39:53
0003 | 2015-01-21 10:39:53
I’d like to add these two tables together to form something like this:
import_id | created | success | error
0001 | 2015-01-21 10:39:53 | 4 | 3
0002 | 2015-01-21 10:39:53 | 1 | 0
0003 | 2015-01-21 10:39:53 | 1 | 1
Solution
You need JOIN and GROUP BY operations
select
i.import_id,
i.created,
s.success,
e.error
from
imports i
join
(select import_id, count(*) as success from importrows where status='success' group by import_id) s on s.import_id=i.import_id
join
(select import_id, count(*) as error from importrows where status='error' group by import_id) e on e.import_id=i.import_id
Answered By – heximal
Answer Checked By – Marie Seifert (BugsFixing Admin)