CODEWITHSUNDEEP
mysqlcountgroup-by
Lorenzo Belfanti
Lorenzo Belfanti

Reputation: 1247

MySQL COUNT() GROUP BY doesn't work

I have a problem in counting by MySQL in a GROUP BY This is the query that does not return the desired result.

SELECT COUNT(bagno)
FROM disposizione_assegnazione_pezze
JOIN pezze 
  ON pezza = id
WHERE id_prodotto_tessuto = 12096
  AND id_collezione = 11 
  AND id_stagione = 22 
  AND id_tema = 1
GROUP BY bagno

The result of the count is 3

This is the pezza table and its primary key is id

Table pezza with results

This is the table disposizione_assegnazione_pezze that has the pezza column which refers to the previous table

Table disposizione_assegnazione_pezze

Why does not return 1 as a result my query?

Question of the problem

I want to count how many different bagno are there

Upvotes: 5

Views: 6264

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

I dont think you need GROUP BY, instead use DISTINCT

 SELECT COUNT(DISTINCT bagno)

SQL DEMO

Check your query without agregatted function COUNT/GROUP BY

enter image description here

As you can see bagno = 55 appear three times, that is why when you group by bagno and count get 3.

Upvotes: 12

Related Questions