troubleshooting Question

mySQL / Wordpress Top 10 query

Avatar of Mike Tew
Mike TewFlag for Canada asked on
DatabasesMySQL ServerWordPress
15 Comments1 Solution515 ViewsLast Modified:
Hi I'm trying to update this top 5 query to work with wordpress & custom mysql tables. I originally wrote it for mssql / asp but I am now getting a syntax error near '*, (SELECT COALESCE...'  in line 1
Select TOP (5) x.*, (SELECT COALESCE(ratingsum,1) / COALESCE(filesinglevote,1)) as ratingavg 
FROM ( SELECT (SELECT meta_value FROM wp_postmeta AS G WHERE G.meta_value = a.filename AND G.meta_key = 'clarge_image') AS filename , (SELECT SUM(rating) AS Expr1 FROM wp_galleryvote AS GV WHERE GV.filename = a.filename) AS 
ratingsum, (SELECT COUNT(*) AS Expr2 FROM wp_galleryvote AS GV WHERE GV.filename = a.filename) AS filesinglevote, (SELECT COUNT(*) AS Expr3 FROM wp_galleryvote AS GV WHERE GV.filename is not null) AS filevotestotal, (SELECT 
post_title FROM wp_posts AS G INNER JOIN wp_postmeta P ON G.ID = P.post_id WHERE P.meta_value = a.filename AND G.meta_key = 'clarge_image' ) AS thecaption FROM wp_galleryvote AS a GROUP by filename) x WHERE x.filename IS NOT NULL 
ORDER BY ratingavg DESC, filesinglevote DESC
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 15 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros