-2

Right now the statement works, but I am only getting 1 category, and the correct amount of total categories that exist. When I remove the count, I get all 9 of my unique categories. How do I make it so that all of the categories are queried? I dont know why adding a count stops the query after one iteration.

$sql="SELECT DISTINCT category, COUNT(category) FROM tasks WHERE status=?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('s',$_POST['status']);
$stmt->execute();
$stmt->bind_result($category,$count);
    while($stmt->fetch()){
echo $category;
}
echo $count;
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Center Truth
  • 80
  • 6
  • 1
    It's not clear exactly what you're result you're trying to obtain. What are you trying to count? – Nick Apr 18 '20 at 04:30
  • I want to count all of the category values given the parameter (status=?), but i also want to echo out each unique category. so say there are 20 categories, but 9 are unique, i want to echo 9 unique categories and the number 20 – Center Truth Apr 18 '20 at 04:33
  • I Googled for: `mysql get distinct values and their count site:stackoverflow.com` and was met with a seemingly endless sea of results. Please research more before posting a new question here. – mickmackusa Apr 18 '20 at 04:35
  • i have been searching... the `https://stackoverflow.com/questions/1346345/mysql-count-the-occurrences-of-distinct-values` you just posted, when doing that suggestion. my results where ALL categories with the amount of occurrences of EACH. i want to echo all categories with a TOTAL SUM of ALL the categories – Center Truth Apr 18 '20 at 04:37
  • I would suggest to do that in 2 queries – Your Common Sense Apr 18 '20 at 04:41
  • Your query looks like this `SELECT COUNT(DISTINCT category) from table_name WHERE status=?";` – A.A Noman Apr 18 '20 at 04:42
  • 1
    @Nick I am not being grumpy hammerman right now. I don't see how these duplicates do not solve the question asked. I will be happy to use my golden prybar if you can explain my failure to me. Sorry I went away, I'm doing a deployment for work. – mickmackusa Apr 18 '20 at 04:50
  • 1
    @mickmackusa OP doesn't want to count the distinct values. They want to count the total number of values *and in the same query* return all the distinct values. Take a look at the answer I posted before you closed, none of the dupes are like that. – Nick Apr 18 '20 at 04:52
  • 1
    @Nick to be honest, he can simply sum up the counts in PHP using any of those – Your Common Sense Apr 18 '20 at 04:53
  • 1
    @YourCommonSense you can't sum the total count if you only return the distinct values. So then you would have to return all the values and in PHP only count the distinct ones. As you said, probably simpler to do in two queries. – Nick Apr 18 '20 at 04:57
  • @Nick so DON'T return "only distinct values" then? Just use one of suggested solutions that return BOTH value and count. THEN sum up the counts – Your Common Sense Apr 18 '20 at 04:58
  • Can someone show me a primitive db-fiddle that demonstrates how my 5 duplicates do not satisfy the requirement? I keep re-reading the Q & A, and I just don't see what others are talking about. I am with YCS here. Just call `array_sum()` on the `COUNT()` column of the result set in php-land. It wouldn't make sense to repeat the total sum on each distinct row. – mickmackusa Apr 18 '20 at 04:59
  • 1
    @mickmackusa https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=4db823e31c135334d6f3f5f4ee27d4c6 – Nick Apr 18 '20 at 05:03
  • The links you provided give results like. value(occurrence),value(occurrence),etc.... I want just the distinct values, then the total occurrences of all values in a lump sum, I will edit post to show desired result – Center Truth Apr 18 '20 at 05:04
  • 1
    @CenterTruth why can't you use the code from the first linked answer and then just add `$total +=$count;` to the while loop and then just `echo $total;`? what's wrong with it? – Your Common Sense Apr 18 '20 at 05:06
  • 1
    @YourCommonSense indeed that does work, I've added it as a possible PHP solution. – Nick Apr 18 '20 at 05:07
  • nothing is wrong with doing that, but not a single answer of any link suggests to do a php count. sure its easy for you guys to just know to do that, but im really new to PHP so i dont know how to do that. just getting my question closed and getting links to answers that dont give me the result doesnt teach me PHP very well – Center Truth Apr 18 '20 at 05:09
  • @Center I initially misunderstood your requirements because there wasn't (and still isn't) a [mcve]. I didn't understand that you needed to sum the counts. My duplicates handle the part where your query lets you down. I can very easily find another duplicate which shows you how to sum a column in a result set. https://meta.stackoverflow.com/q/333952/2943403 – mickmackusa Apr 18 '20 at 05:12
  • thats great, im sure you can. but only nick was interested in actually getting to the bottom of it. from my experience, nick is someone who has helped me in many occasions. where as, if my question is closed it is commonly from your common sense. not saying anyones right or wrong, its just what ive noticed – Center Truth Apr 18 '20 at 05:27
  • 1
    What is best for Stackoverflow is to help people without generating unnecessary content. If we can get you to a solution without answering, it is our thankless duty to do so. We earn nothing by closing -- we are doing what is best for the system instead of aiming for more points. Yes, people who close questions (like YCS and myself are painted poorly), but it is actually the way that is best for all. You don't need your question to survive, you just need a resolution so that you can continue your work. – mickmackusa Apr 18 '20 at 06:24
  • @Center another technique that just occurred to me is the use of MySQL's WITH ROLL UP. You may want to have a quick research of that if you are curious. Example: https://stackoverflow.com/a/52876224/2943403 – mickmackusa Apr 18 '20 at 06:26
  • 1
    thanks for your help and listening. i appreciate it, thanks for the tips and info as well – Center Truth Apr 18 '20 at 16:45

1 Answers1

1

You can't aggregate (COUNT) and SELECT DISTINCT in the same query without using a subquery. For example,

SELECT DISTINCT category, (SELECT COUNT(category) FROM tasks WHERE status = ?) AS count
FROM tasks
WHERE status = ?

You can possibly do this a little more readily in PHP by selecting all the categories and post processing the result:

$sql = "SELECT category, COUNT(*) AS count FROM tasks WHERE status=? GROUP BY category";
$stmt = $conn->prepare($sql);
$stmt->bind_param('s',$_POST['status']);
$stmt->execute();
$stmt->bind_result($category,$count);
$counts = 0;
while ($stmt->fetch()) {
    echo $category;
    $counts += $count;
}
echo $count;
Nick
  • 138,499
  • 22
  • 57
  • 95
  • i dont think i want to count them individually with each, i want to echo each category, and just count the total amount of categories given the parameter – Center Truth Apr 18 '20 at 04:30
  • There we go. I would do it this way. No subqueries and just count afterward. Otherwise you have redundant data in your result set (demonstrated in Nick's fiddle). – mickmackusa Apr 18 '20 at 05:08
  • agreed, the php count is very simple a lot less code – Center Truth Apr 18 '20 at 05:11