2

I was under the impression that in MySQL, GROUP BY supported the use of column names or column alias'.

I have some fairly simple SQL which is doing a cross join between 2 tables and then a left join to get some matching data (if it exists), and concatenating up matching data. This was giving strange results. Finally realised that the problem appears to be my use of alias names rather than the actual column names.

With alias names it seems to have treated those lines rows with a non matched row on the left join differently. Hence getting 2 rows returned by the group by despite sharing the same GROUP BY fields.

The 2 queries are, firstly grouping by column alias':-

SELECT a.id AS item_id, a.code AS item_code, GROUP_CONCAT( CONCAT_WS( ":", c.name, IFNULL( d.value, "void" ) ) 
ORDER BY c.name ) AS AllAnaly
FROM item a
CROSS JOIN item_analy c
LEFT OUTER JOIN item_analy_value d ON a.id = d.item_id
AND c.id = d.analy_id
GROUP BY item_id, item_code;

and grouping by column names

SELECT a.id AS item_id, a.code AS item_code, GROUP_CONCAT( CONCAT_WS( ":", c.name, IFNULL( d.value, "void" ) ) 
ORDER BY c.name ) AS AllAnaly
FROM item a
CROSS JOIN item_analy c
LEFT OUTER JOIN item_analy_value d ON a.id = d.item_id
AND c.id = d.analy_id
GROUP BY a.id, a.code;

I have knocked up an SQL fiddle that demonstrates this:-

http://sqlfiddle.com/#!2/35a69

I am missing something with how MySQL treats alias' in GROUP BY clauses?

Kickstart
  • 21,403
  • 2
  • 21
  • 33
  • Group by is not working on alias of any column!! Detail ans: http://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by – Jain Feb 10 '14 at 17:39
  • @Jain - MySQL is one flavour where this should work - https://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html – Kickstart Feb 10 '14 at 19:35

1 Answers1

2

I guess what is happening is that MySQL is defaulting to use the non aliased column name that matches the GROUP BY column specified, and if one isn't found it uses alias names instead. in your example the query with column names is right while when you use aliases it takes as not aliased .

while this query will also work like you grou by column names.

  SELECT a.id AS item_id, a.code AS item_code, GROUP_CONCAT( CONCAT_WS( ":", c.name, IFNULL( d.value, "void" ) ) 
 ORDER BY c.name ) AS AllAnaly
 FROM item a
 CROSS JOIN item_analy c
 LEFT OUTER JOIN item_analy_value d ON a.id = d.item_id
 AND c.id = d.analy_id
echo_Me
  • 37,078
  • 5
  • 58
  • 78
  • Think you are on the right lines. With my query I have given an alias name to a column which happens to be the same as one of the columns that I am using for a join but which I disregard in the actual SELECT. Seems MySQL has chosen to GROUP BY a column which isn't being returned, rather than an alias column of that name which is being returned. – Kickstart Feb 10 '14 at 19:42