[SOLVED] Generating new columns with subqueries

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)

Leave a Reply

Your email address will not be published.