CODEWITHSUNDEEP
mysql
amathew
amathew

Reputation: 366

Selecting a count on two separate conditions

Let's say I have the following data.

id  name_id   completed
1    10          1
2    10          0
3    15          1
4    10          0
5    20          1
6    15          0
7    20          1 
8    15          0

I'm trying to find a count by the name id, which is pretty simple

SELECT name_id, COUNT(*) FROM db 
GROUP BY name_id

Now, I have a second component which I want to include in the query.

For name_id 10, I want to count just those values where completed is 1. For the other name_id's, I want to select them regardless of whether they are 0 or 1.

So I should end up with:

name_id     count(*)
10            1
15            3
20            2

Name_id 10 only has a count of 1 because it's just the 1 which is completed, while the other counts include both 0 and 1.

Can anyone help with this task.

Thanks!

Upvotes: 3

Views: 169

Answers (4)

Ed Gibbs
Ed Gibbs

Reputation: 26343

Count when name_id is not 10. If it is 10, count when completed = 1:

SELECT
 name_id,
 COUNT(CASE WHEN name_id <> 10 or completed = 1 THEN 1 END)
FROM db
GROUP BY name_id

Upvotes: 1

Christian
Christian

Reputation: 1258

SELECT name_id, COUNT(*) FROM db 
WHERE name_id != 10 or completed = 1
GROUP BY name_id

Upvotes: 1

Jonathan S.
Jonathan S.

Reputation: 2228

Exclude the rows where name_id = 10 and completed = 0:

SELECT name_id, COUNT(*) FROM db 
WHERE NOT (completed = 0 AND name_id = 10)
GROUP BY name_id

Upvotes: 3

Taryn
Taryn

Reputation: 247710

You can use a CASE expression inside of your aggregate function. 

SELECT name_id, 
  sum(case 
          when name_id = 10 
          then case when completed = 1 then 1 else 0 end
          else 1 end)  Total
FROM db 
GROUP BY name_id;

See SQL Fiddle with Demo.

Upvotes: 4

Related Questions