Trying to rank the most popular entries

Databases
1

I track whenever a person marks a testimonial as a favorite in the favorites table. Today I decided to show a report of which testimonials have been marked as a favorite the most. This query works fine:

select tID, count(*) as number from favorites group by tID order by number desc;

So now I want to display the results on the website for my readers to see. In this case I need the actual title of each of these testimonials:

select t.title, f.tID, count (*) as number
from testimonies t inner join favorites f
on t.tID = f.tID
where number > 3
group by f.tID
order by number desc;

Can someone help me see why I’m getting a 1064 mySQL error?

Thanks!

2

can’t use the column alias in the WHERE clause

you want a HAVING clause instead

:slight_smile:

3

Ugh. This didn’t work either. What am I doing wrong r937?

select t.summary, f.tID, count (*) as number
from testimonies t inner join favorites f
on t.tID = f.tID
group by f.tID
having number > 3
order by number desc;

4

sorry, i don’t understand “didn’t work either” without a more substantial clue as to what went wrong

:slight_smile:

5

Meaning I continue to get a 1064 mySQL error.

6

isn’t there an actual error message along with the error number?

maybe you could do a SHOW CREATE TABLE for both tables

7

Error Code: 1064
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 1

8

okay, i’m gonna guess you can’t have a space between COUNT and COLOR=“red”[/COLOR]

:slight_smile:

9

You rock Rudy!