CODEWITHSUNDEEP
sqldatabaseselectsql-server-2008-r2
guitarzero
guitarzero

Reputation: 585

Find number of same rows in a column related to a specific row value in another column

I'm trying to make a query to find out how many persons have an activity in status 3. I assume I need either a COUNT or SUM, but it was a long time since I did this before and I've tried searching alot, but am still confused. Let's say I have the following.

Activity table:

activity_id  status
1             3
2             3
3             1

Person_activity table:

person_id  activity_id
1            1
1            2
2            2
2            3 
3            3

I want the query to output:

person_id   no_of_status_3
1             2
2             1
3             0

Any suggestions will be much appreciated. Thanks in advance.

Upvotes: 2

Views: 49

Answers (2)

Kombajn zbożowy
Kombajn zbożowy

Reputation: 10693

SELECT pa.person_id, count(a.activity_id)
FROM person_activity pa
LEFT JOIN activity a ON (pa.activity_id = a.activity_id AND a.status = 3)
GROUP BY pa.person_id

Upvotes: 1

Lamak
Lamak

Reputation: 70648

This should do:

SELECT  PA.person_id,
        COUNT(A.activity_id) no_of_status_3
FROM Person_activity PA
LEFT JOIN ( SELECT *
            FROM Activity
            WHERE status = 3) A
    ON PA.activity_id = A.activity_id
GROUP BY PA.person_id

Here is an sqlfiddle with a demo of this. And the results are:

╔═══════════╦════════════════╗
║ PERSON_ID ║ NO_OF_STATUS_3 ║
╠═══════════╬════════════════╣
║         1 ║              2 ║
║         2 ║              1 ║
║         3 ║              0 ║
╚═══════════╩════════════════╝

Upvotes: 4

Related Questions