Jump to content

Quick help. This GROUP BY query not working properly.


imgrooot

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;

 

Link to comment
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

 

Link to comment
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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.