SELECT with a COUNT of another SELECT

Question

I have a table in SQL that is a list of users checking in to a website. It looks much like this:

id | date | status 
------------------

Status can be 0 for not checking in, 1 for checked in, 2 for covered, and 3 for absent.

I'm trying to build one single query that lists all rows with status = 0, but also has a COUNT on how many rows have status = 3 on each particular id.

Is this possible?

Answer 1

MySQL VERSION

just join a count that is joined by id.

SELECT t.*, COALESCE(t1.status_3_count, 0) as status_3_count
FROM yourtable t
LEFT JOIN 
(   SELECT id, SUM(status=3) as status_3_count
    FROM yourtable
    GROUP BY id
) t1 ON t1.id = t.id
WHERE t.status = 0

note: this is doing the boolean sum (aka count).. the expression returns either true or false a 1 or a 0. so I sum those up to return the count of status = 3 for each id

SQL SERVER VERSION

SELECT id, SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END) as status_3_count
FROM yourtable
GROUP BY id

or just use a WHERE status = 3 and a COUNT(id)

Answer 2

Try a dependent subquery:

SELECT t1.*,
       (  SELECT count(*) 
          FROM sometable t2
          WHERE t2.id = t1.id 
            AND t2.status = 3
       ) As somecolumnname
FROM sometable t1
WHERE t1.status=0