CODEWITHSUNDEEP
mysqljoin
chopTheWood
chopTheWood

Reputation: 1

inner join with COUNT() and GROUP BY

I am using phpMyAdmin to test this query but keep getting a syntax error. I've tried looking it up in the MySql manual and trying other syntactical possibilities but I've gotten older in this process. Thanks for your help

SELECT image_title, image_id, COUNT(other_sales.*) FROM art INNER JOIN other_sales ON (art.image_id=other_sales.image_id) GROUP BY (other_sales.image_id);

MySQL said: Documentation

Documentation

1052 - Column 'image_id' in field list is ambiguous

Ultimately, I want to count the number of times a specific number (image_id) occurs in the 'other_sales' table

Upvotes: 0

Views: 1929

Answers (3)

chopTheWood
chopTheWood

Reputation: 1

Yes, I see that now. There are 2 problems with my original code.

  1. I should have chosen the specific column name instead of using *

  2. image_id is a column name that appears in both tables I am accessing. That will cause an "ambiguous" error.

Below is the original code and the corrected code:

original: SELECT image_title, image_id, COUNT(other_sales.*) FROM art INNER JOIN other_sales ON (art.image_id=other_sales.image_id) GROUP BY (other_sales.image_id);

corrected: SELECT image_title, art.image_id, COUNT(other_sales.image_id) FROM art INNER JOIN other_sales ON (art.image_id=other_sales.image_id) GROUP BY (other_sales.image_id);

thanks for the help

Upvotes: 0

Jeroen
Jeroen

Reputation: 161

the tables art and other sales probably both have the column image_id. specify the table before the column like art.imageid or asign a alias to the table and then to the column like so

SELECT o.image_title, o.image_id, COUNT(*)
FROM art a JOIN other_sales o ON (art.id=other_sales.image_id) 
GROUP BY (o.image_id)

Upvotes: 1

O. Jones
O. Jones

Reputation: 108676

To troubleshoot these 1064 errors:

The error message gives a snippet of your query. The first character of the snippet is the first character the MySQL interpreter could not understand.

So in the case of your query, it's 

SELECT image_title, image_id, COUNT(other_sales.*) FROM art INNER JOIN ...
ggggggggggggggggggggggggggggggggggggggggggggggggbbbbbbbbbbbbbbbbbbbbbbbbbbb

where g means good and b means bad.

Your actual problem: you can't put more than one value in a COUNT() function. You tried to put COUNT(something.*) which makes no sense to count.

Notice that COUNT(*) is a special case meaning just count the rows.

Upvotes: 1

Related Questions