32

Why I get #1060 - Duplicate column name 'id'

SELECT COUNT(*) FROM (SELECT * FROM `tips` `t` LEFT JOIN
tip_usage ON tip_usage.tip_id=t.id GROUP BY t.id) sq
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Pentium10
  • 204,586
  • 122
  • 423
  • 502
  • Are you aware, that since it's a LEFT join, rows in joined table do not influence number of rows returned by COUNT(*)? You might just as well do `SELECT COUNT(*) FROM tips` – Mchl Jan 27 '11 at 11:22
  • No! Wait!... there's a GROUP BY... but no aggregating function... this query just makes no sense. – Mchl Jan 27 '11 at 11:24
  • Is `id` a `PRIMARY KEY` on `tips`? If you don't have a record for a tip in `tip_usage`, should this tip be counted? – Quassnoi Jan 27 '11 at 11:24
  • @Mchl: it's the same as `COUNT(DISTINCT)`. It could make sense if there were duplicates on `t.id`. – Quassnoi Jan 27 '11 at 11:25
  • If you need `COUNT(DISTINCT ..)` you use `COUNT(DISTINCT ..)` not some trick that might or might not work depending on SQL mode set. http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_only_full_group_by – Mchl Jan 27 '11 at 11:29

4 Answers4

44

Probably because the * in select * selects two columns with the same name from tip_usage and tips.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 1
    ... and "any columns in the subquery select list [must have](http://dev.mysql.com/doc/refman/5.7/en/from-clause-subqueries.html) unique names." – x-yuri Dec 10 '14 at 17:26
12

Probably it's because the inner select yields two columns with the name id. Since you are not using those columns, you can just change the select to:

SELECT COUNT(*) FROM (SELECT t.id FROM `tips` `t` 
LEFT JOIN tip_usage ON tip_usage.tip_id=t.id 
GROUP BY t.id) sq
Klaus Byskov Pedersen
  • 117,245
  • 29
  • 183
  • 222
0

Your query is equivalent to this:

SELECT  COUNT(DISTINCT id)
FROM    tips

, there is no need in a join.

Are you sure you didn't want an INNER JOIN instead?

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
0

Had the same problem, renaming into select clause saved me

SELECT people.id, vehicle.id ...

I renamed it with AS keyword

SELECT people.id AS person_id, vehicle.id ...
Pipo
  • 4,653
  • 38
  • 47