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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

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.