MySQL COUNT() GROUP BY doesn't work

Question

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

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

Why does not return 1 as a result my query?

Question of the problem

I want to count how many different bagno are there

Im confussed what is your current result and your desire result? — Juan Carlos Oropeza, Dec 13 '16 at 14:14
if you're trying to query 2 tables, then you need to select both tables. — Funk Forty Niner, Dec 13 '16 at 14:15
http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — Juan Carlos Oropeza, Dec 13 '16 at 14:16
i.e.: `FROM disposizione_assegnazione_pezze, table2` as an addendum to my first comment. — Funk Forty Niner, Dec 13 '16 at 14:17
@Fred-ii- But the two tables can be joined using the key `pezza` -> `id` — Lorenzo Belfanti, Dec 13 '16 at 14:20
I think `COUNT` should be `3` not `1` but put the data as text so we can test it. — Juan Carlos Oropeza, Dec 13 '16 at 14:20
@JuanCarlosOropeza I've updated the example posted the query result is 3, but I expect 1.Because I want to count how many times `bagno` there differently, In this case 1. — Lorenzo Belfanti, Dec 13 '16 at 14:24
Possible duplicate of [How to use count and group by at the same select statement](http://stackoverflow.com/questions/2722408/how-to-use-count-and-group-by-at-the-same-select-statement) — Adam Millerchip, Dec 13 '16 at 14:42

Juan Carlos Oropeza · Accepted Answer · 2016-12-13T14:36:51.620

11

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

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

edited Dec 13 '16 at 14:36

answered Dec 13 '16 at 14:24

Juan Carlos Oropeza

47,252
12
78
118

So it works, but I do not understand why. If I use the `GROUP BY` I group all results to have them different, in my case it will return only one result, so if you should count with `COUNT` function I think should be corrected. – Lorenzo Belfanti Dec 13 '16 at 14:28
Maybe if you explain what are those fields, or put a bigger example I can give you more detail. – Juan Carlos Oropeza Dec 13 '16 at 14:31
1

Check my edit explanation. That is exactly what `DISTINCT` does – Juan Carlos Oropeza Dec 13 '16 at 14:37
Works great, however, Why if I make a `GROUP BY` and `SELECT *` result only one line? Taking a `COUNT (*)` should not return **1** as a result? Example: http://rextester.com/KNOBO9612 – Lorenzo Belfanti Dec 13 '16 at 14:41
Samething, the only difference is `COUNT(*)` count the number of row not matter what values are there, `COUNT(bagno)` only count rows where `bagno IS NOT NULL` so ignore null rows. – Juan Carlos Oropeza Dec 13 '16 at 14:43

MySQL COUNT() GROUP BY doesn't work

Question of the problem

1 Answers1