SQL COUNT() doesn't work in MySQL

Question

SELECT person.id,
       person.name,
       COUNT(DISTINCT fruit.apple) AS "Red Apple",
       fruit.*
FROM   (SELECT *
        FROM   tree
        ORDER  BY color DESC) AS fruit
       INNER JOIN person
         ON fruit.id = person.id
WHERE  person.name = 'John Smith'

Now, that code is working before I put in the COUNT(DISTINCT fruit.apple) AS "Apple". what is wrong with that?

Thanks in advance

i bet the query returns exactly what you have asked it to return. what is your data? what results do you expect? — Randy
– Randy, Commented Oct 11, 2011 at 16:31
Define "Not working". Also what is the purpose of ORDER BY color DESC in the derived table definition? Also INNER JOIN people ON fruit.id = person.id definitely won't work. The table name changes midway through. — Martin Smith
– Martin Smith, Commented Oct 11, 2011 at 16:31
@MartinSmith : Not working like this mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource — Iseng Aja
– Iseng Aja, Commented Oct 11, 2011 at 16:33
@JohnSmith - Please post your actual query and explain what it is intended to do. — Martin Smith
– Martin Smith, Commented Oct 11, 2011 at 16:38
the color actually date type column, so I have to sort it descending, and.. those answers below are true :D thanks guys! — Iseng Aja
– Iseng Aja, Commented Oct 11, 2011 at 16:41

Marc B · Accepted Answer · 2011-10-11 16:29:41Z

4

You don't have a group by clause.

answered Oct 11, 2011 at 16:29

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vikas Naranje · Accepted Answer · 2011-10-11 16:33:57Z

0

try this

SELECT person.id,
       person.name,
       COUNT(DISTINCT fruit.apple) AS "Red Apple",
       fruit.*
FROM   (SELECT *
        FROM   tree
        ORDER  BY color DESC) AS fruit
       INNER JOIN people
         ON fruit.id = person.id
WHERE  person.name = 'John Smith' 
GROUP BY fruit.apple

answered Oct 11, 2011 at 16:33

Vikas Naranje

2,4126 gold badges31 silver badges41 bronze badges

Collectives™ on Stack Overflow

SQL COUNT() doesn't work in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related