MySQL aggregation error

Question

I'm trying to count all the ids form table3 that are related to the first two tables but I think my SQL code is wrong can some one help me fix it?

Here is the code:

$dbc = mysqli_query($mysqli,"SELECT table1.*, table2.*, COUNT(id) as num, table3.id
                             FROM table1
                             INNER JOIN table2 ON table1.id = table2.id
                             INNER JOIN table3 ON table2.id = table3.id
                             WHERE table2.id = '$id'");

Here is the error message.

1140: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

You know, you could post whatever error message you're getting. Or if you're not getting an error, you can post the output you're getting and the expected output. — lc., May 28 '10 at 06:43

score 0 · Answer 1 · answered May 28 '10 at 06:47

0

Can u try this ?

SELECT COUNT(table3.id) as num
  FROM table3, table2, table1
WHERE table1.id = table2.id 
  AND table2.id = table3.id
  AND table2.id = $id

answered May 28 '10 at 06:47

Adrian Pirvulescu

4,308
3
30
47

score 0 · Answer 2 · edited May 23 '17 at 12:30

You are trying to use an aggregate function, COUNT(), without GROUPing the results first. Also, you'll need to make the "id" column within the COUNT() less ambiguous similar to Adrian's query. Please try the following query:

SELECT table1.*, table2.*, COUNT(table3.id) as num, table3.id
  FROM table3, table2, table1    
WHERE table1.id = table2.id     
  AND table2.id = table3.id    
  AND table2.id = $id  
GROUP BY
  table1.*,
  table2.*,
  table3.id

For further details about aggregate functions and ambiguous column names within queries, check out the MySQL Reference Manual for Group By Functions and Indentifier Qualifiers.

MySQL aggregation error

2 Answers2