Mysql SELECT and COUNT WHERE

Question

I have two tables (lets call them cases and users) structured something like this: Cases

ID|status      |user_id
__|____________|_______  
1 |Open        |6
2 |Closed      |9
3 |Incomplete  |9

Users:

ID| 
__|_____
 6|
 9|

I would like to select a count for the id as well as a count for each one of the status fields.

Currently I have tried:

SELECT COUNT(c.id), COUNT(
(SELECT status from cases where status = 'Open')),
COUNT(
(SELECT status from cases where status = 'Closed')),
COUNT(
(SELECT status from cases where status = 'Incomplete'))
FROM cases as c
JOIN users as u on u.id = c.user_id

I am getting an error of query returning more than one row.

This is what I am trying to achieve

ID| Open | Closed | Incomplete |user_id
__|______|________|____________|__________
 2|0     |1       |1           |9

RECAP: A count for each user id that counts how many id's, and a count for each status.

M Khalid Junaid · Accepted Answer · 2014-01-16 16:45:04Z

3

You can try this with sum

SELECT COUNT(c.id), 
SUM(c.status = 'Open') Open ,
SUM(c.status = 'Closed') Closed ,
SUM(c.status = 'Incomplete') Incomplete,
u.id user_id
FROM cases as c
JOIN users as u on u.id = c.user_id
GROUP BY u.id

answered Jan 16, 2014 at 16:45

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

This seems like such a reasonable answer. It is just really hard to tell if this actually answers the question.

Collectives™ on Stack Overflow

Mysql SELECT and COUNT WHERE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related