CODEWITHSUNDEEP
phpmysql
jitendrapurohit
jitendrapurohit

Reputation: 9675

mysql group by column error when alias specified

Following mysql query gives an error: 'email.id' isn't in GROUP BY, 1055

SELECT contact_a.id as contact_id,
  contact_a.sort_name  as `sort_name`,
  contact_a.display_name  as `display_name`,
  phone.id as phone_id,
  phone.phone_type_id as phone_type_id,
  phone.phone as `phone`,
  email.id as `email_id`,
  email.email as `email`,

FROM contact_table contact_a
  LEFT JOIN email ON (contact_a.id = email.contact_id)
  LEFT JOIN phone ON (contact_a.id = phone.contact_id)

 GROUP BY contact_a.id,
  `sort_name`,
  `display_name`,
  `phone_id`,
  `phone_type_id`,
  `phone`,
  `email_id`,
  `email`";

eventhough I've specified the alias of email.id in GROUP BY.

If I replace the alias email_id in GROUP BY with email.id, it works.

But why can't I use the alias in GROUP BY and how can it work for other alias like phone_id, phone_type_id, email etc ?

Upvotes: 3

Views: 189

Answers (1)

Shadow
Shadow

Reputation: 34231

Since mysql extends the mysql standard and allows you to use column aliases in the group by clause, the only think you need to pay attention to is not to use the same alias as an existing field name in any of the tables referenced in the from clause.

In case you use the same alias as an existing field, then mysql will interpret the group by clause as if it refferred to the existing field, not to the aliased one. This causes the above error message.

Upvotes: 2

Related Questions