0

This is the query and result without using grouping by: Query without grouping by

SELECT count(T2.id_korisnika) As brojPonuda, CONCAT(T2.ime, ' ', T2.prezime) AS naziv 
FROM ponude T1, tblKorisnici T2 
WHERE T1.id_korisnika = T2.id_korisnika

And this is the query and result with grouping by: Query with grouping by

SELECT count(T2.id_korisnika) As brojPonuda, CONCAT(T2.ime, ' ', T2.prezime) AS naziv 
FROM ponude T1, tblKorisnici T2 
WHERE T1.id_korisnika = T2.id_korisnika 

Group by naziv

My question is why are they opposite of what's supposed to happen? Isn't group by supposed to group multiple rows with same values and produce a result of an aggregate function alongside the grouped value? So why in this case it's doing the opposite of that? When I don't use group by it actually gets automatically grouped by and if I use it I get a weird result where the values are separated, also I don't understand why in some rows there is a 2 and all the others are a 1

Update: it seems to work when I replace the concat and the "naziv" column with id_korisnika. So maybe mysql can't group properly with strings?

Query with replaced column

Here is the data in the database:

tblKorisnici

ponude

NezukoChan
  • 1
  • 2
  • The first query returns one row - a summary of all data. The second breaks down the summary, one row for each group. – Bohemian Apr 07 '20 at 00:17
  • @Bohemian I know that, but I don't understand why does that happen, shouldn't it be opposite? – NezukoChan Apr 07 '20 at 00:26

2 Answers2

0

Your first query should be written as:

SELECT count(T2.id_korisnika) As brojPonuda,
       CONCAT(T2.ime, ' ', T2.prezime) AS naziv 
FROM ponude T1 JOIN
     tblKorisnici T2 
     ON T1.id_korisnika = T2.id_korisnika ;

This is an aggregation query with no GROUP BY. Hence, it theoretically returns one row -- treating the entire result set (from the JOIN) as a single group. That said, the query is syntactically incorrect, because naziv is not in the GROUP BY.

The second query:

SELECT count(T2.id_korisnika) As brojPonuda,
       CONCAT(T2.ime, ' ', T2.prezime) AS naziv 
FROM ponude T1 JOIN
     tblKorisnici T2 
     ON T1.id_korisnika = T2.id_korisnika
GROUP BY naziv;

This simply says to produce a result set where the rows are defined by distinct values of naziv. Each of these rows is summarized from the result of the JOIN and the count is the first column.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Ok but those values of "naziv" are all the same, none are different it's all the same value so they are not distinct, so shouldn't they all be grouped and get the same result as in the first query? – NezukoChan Apr 07 '20 at 00:27
  • It seems to be a problem with the naziv column, maybe mysql can't group by properly with strings? I've replaced the grouping by naziv with grouping by id_korisnika and now it works properly, but I need it to actually group by the naziv coulmn, so what should I do? @Gordon Linoff – NezukoChan Apr 07 '20 at 00:40
  • @NezukoChan . . . `GROUP BY` works fine with strings. You probably have hidden characters -- such as trailing spaces or something else. – Gordon Linoff Apr 07 '20 at 00:47
  • there is only 1 entry with that name in the database, I've updated the original post with data from the database @Gordon Linoff – NezukoChan Apr 07 '20 at 00:53
  • I will try to update the mysql server version @Gordon Linoff – NezukoChan Apr 07 '20 at 01:00
  • The issue still persists in mysql 8.0 @Gordon Linoff – NezukoChan Apr 07 '20 at 02:34
  • It seems to be the issue with the alias in group by clause. If i replace the alias with the full concat function then it works normally but if I use the alias instead of concat then it doesn't work properly @Gordon Linoff – NezukoChan Apr 07 '20 at 02:41
  • I discovored what the issue was, the problem was I had another column named "naziv" in my "ponude" table and the group by apparently prioritizes that name over the concat alias in select so it was actually grouping by that column instead of the alias in the select. – NezukoChan Apr 07 '20 at 02:47
  • @NezukoChan . . . You can repeat the expression in the `GROUP BY`. – Gordon Linoff Apr 07 '20 at 10:36
0

The issue was I had another column named "naziv" in my "ponude" table and the sql apparently prioritized that name instead of the alias and therefore the results were incorrect.

SQL - using alias in Group By

NezukoChan
  • 1
  • 2