Jump to content
imgrooot

Quick help. This GROUP BY query not working properly.

Recommended Posts

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;

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.