ASK83 Posted June 9, 2009 Share Posted June 9, 2009 I'm trying to count and mathematically manipulate two different fields in two different tables as a variable in a query. Basically it's a user posting system and I'm ordering the query by the most popular submissions using two tables "up" and "down" and setting a variable in the query equal to up minus down. The query looks like this.... $query = "SELECT news.id AS ni, COUNT(up.news_id - down.news_id) as c, user.id, user.username FROM news, user LEFT JOIN (up) ON (up.news_id = news.id) LEFT JOIN (down) ON (down.news_id = news.id) WHERE user.id = news.user_id GROUP BY news.id ORDER BY c DESC "; Quote Link to comment Share on other sites More sharing options...
trq Posted June 9, 2009 Share Posted June 9, 2009 And your question / problem is? Quote Link to comment Share on other sites More sharing options...
fenway Posted June 9, 2009 Share Posted June 9, 2009 The problem is your use of group by & mixing comma and JOIN, amongst other things. Quote Link to comment Share on other sites More sharing options...
ASK83 Posted June 9, 2009 Author Share Posted June 9, 2009 Sorry, the problem is that it does not subtract and the downs from the ups - basically the syntax on the COUNT(up.news_id - down.news_id) as c, Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted June 11, 2009 Share Posted June 11, 2009 Maybe I'm going solo on this one but using the COUNT() aggregate function in your situation doesn't make sense. Remove it. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.