CODEWITHSUNDEEP
mysqlcountsubquery
johnnytee
johnnytee

Reputation: 169

Mysql count of a subquery column with having

I have a table called giveaways and each giveaway can have contestants. I am trying to get the number of giveaways without winners. The contestants table has a field called winner that is 1 or 0.

My data looks like:

Giveaway Table

| id | name       |
|----|------------|
| 1  | Giveaway 1 |
| 2  | Giveaway 2 |

Contestant Table

| id | giveaway_id|winner|
|----|------------|------|
| 1  | 1          | 0    |
| 2  | 1          | 0    |
| 3  | 2          | 0    |

This is my query :

SELECT (SELECT COUNT(id) FROM contestants c 
        WHERE c.giveaway_id = g.id AND winner = 1) as winners
FROM giveaways g
having winners =  0

Right now this will return multiple rows, I want to get the count of rows. I tried wrapping a count() around the winners subquery but that did not work.

In the example above this would be returned:

Results
| winners |
|---------|
| 0       |
| 0       |

I want just the count of rows which would be 2.

What's a better approach? Thx

Upvotes: 0

Views: 38

Answers (1)

GMB
GMB

Reputation: 222402

I am trying to get the number of giveaways without winners.

Use NOT EXISTS with a correlated subquery :

SELECT
    COUNT(*)
FROM
    giveaways g
WHERE NOT EXISTS (
    SELECT 1 FROM contestants WHERE giveaway_id = g.id AND winner = 1
)

The subquery ensures that there is no winning contestant for each giveaway to count.

Upvotes: 1

Related Questions