CODEWITHSUNDEEP
mysqlsqlselect
mattklamp
mattklamp

Reputation: 293

SELECT with a COUNT of another SELECT

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?

Upvotes: 2

Views: 9464

Answers (3)

John Ruddell
John Ruddell

Reputation: 25862

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)

Upvotes: 8

AdamMc331
AdamMc331

Reputation: 16710

You can use a join for this. Write one query that will get all rows with a status zero:

SELECT *
FROM myTable
WHERE status = 0;

Then, write a subquery to get counts for the status of 3 for each id, by grouping by id:

SELECT COUNT(*)
FROM myTable
WHERE status = 3
GROUP BY id;

Since you want all the rows from the first table (at least that's what I am picturing), you can use a LEFT JOIN with the second table like this:

SELECT m.id, m.status, IFNULL(t.numStatus3, 0)
FROM myTable m
LEFT JOIN (SELECT id, COUNT(*) AS numStatus3
      FROM myTable
      WHERE status = 3
      GROUP BY id) t ON m.id = t.id
WHERE m.status = 0;

The above will only show the count for rows containing an id that has status 0. Hopefully this is what you are looking for. If it is not, please post some sample data and expected results and I will help you try to reach it. Here is an SQL Fiddle example.

Upvotes: 1

krokodilko
krokodilko

Reputation: 36107

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

Upvotes: 3

Related Questions