Quick help. This GROUP BY query not working properly.

[imgrooot]

I have a simply query where I want show how many unique contests are there. So if I have 100 entries in a single contest, then each entry row will have the same contest id in this example.

So even though there are 100 entries, they all belong to the same contest id. That means the echo of $total_contests should be 1. But instead I am getting the same count as the entry rows, which is 100.  What am I doing wrong?

$global_user_id = 1;

$count_contests = $db->prepare("SELECT COUNT(*) FROM entries WHERE user_id = :user_id GROUP BY contest_id");
$count_contests->bindParam(':user_id', $global_user_id);
$count_contests->execute();
$total_contests = $count_contests->fetchColumn();

echo $total_contests;

 

[Zane]

You need to include the contest_id in your SELECT clause.

SELECT contest_id, COUNT(*) FROM entries WHERE user_id = :user_id GROUP BY contest_id

 

[imgrooot]

7 minutes ago, Zane said:

You need to include the contest_id in your SELECT clause.

SELECT contest_id, COUNT(*) FROM entries WHERE user_id = :user_id GROUP BY contest_id

 

Well...that was simple and a quick fix. Works now.

Thank you.

[Barand]

That will give the number of entries in each contest. For the number of contests

SELECT COUNT(DISTINCT contest_id) as contests 
FROM entries 
WHERE user_id = :user_id

or, for all users

SELECT user_id
     , COUNT(DISTINCT contest_id) as contests 
FROM entries 
GROUP BY user_id

 

[imgrooot]

21 hours ago, Barand said:

That will give the number of entries in each contest. For the number of contests

SELECT COUNT(DISTINCT contest_id) as contests 
FROM entries 
WHERE user_id = :user_id

or, for all users

SELECT user_id
     , COUNT(DISTINCT contest_id) as contests 
FROM entries 
GROUP BY user_id

 

 

Ah yes this is a better solution. I've tested it and it works. 

Thanks.