-1

I have two tables: `jobs' & 'clients'.

The clients table looks like this:

/----+------\
| id | name |
+----+------+
| 1  | Ben  |
| 2  | Max  |
\----+------/

And the jobs table:

/----+-----------+--------\
| id | client_id | status |
+----+-----------+--------+
| 1  |     1     |  alpha |
| 2  |     1     |  beta  |
| 3  |     1     |  beta  |
| 4  |     2     |  beta  |
\----+-----------+--------/

I'm looking to create a statement that will return the name of the client along with the number of times each status appears, like this:

/------+-------+------\
| name | alpha | beta |
+------+-------+------+
| Ben  |   1   |   2  |
| Max  |   0   |   1  |
\------+-------+------/

I do not require there to be a 0 where no values exist though.

I have tried SELECT name, (SELECT status FROM jobs WHERE client_id = clients.id) FROM clients but it returns more than one row in the sub-query.

Blease
  • 1,380
  • 4
  • 38
  • 64

1 Answers1

4

Here's an example with a solution: http://sqlfiddle.com/#!9/80593/3

create table clients (id int, name varchar(10));
insert into clients values (1, 'Ben'), (2, 'Max');

create table jobs (id int, client_id int, status varchar(10));
insert into jobs values (1, 1, 'alpha'), (2, 1, 'beta'), (3, 1, 'beta'), (4, 2, 'beta');

select 
  clients.name, 
  sum(case when jobs.status = 'alpha' then 1 else 0 end) alpha,
  sum(case when jobs.status = 'beta' then 1 else 0 end) beta
from jobs
inner join clients 
  on jobs.client_id = clients.id
group by clients.name;
zedfoxus
  • 35,121
  • 5
  • 64
  • 63
  • 1
    IMHO you can remove `CREATE` and `INSERT` statements from your answer. They are in your fiddle. but there is no need to put it as a part of answer. – Alex Sep 10 '15 at 17:30