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 "; Link to comment https://forums.phpfreaks.com/topic/161482-counting-and-sum-from-different-tables/ Share on other sites More sharing options...
trq Posted June 9, 2009 Share Posted June 9, 2009 And your question / problem is? Link to comment https://forums.phpfreaks.com/topic/161482-counting-and-sum-from-different-tables/#findComment-852204 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. Link to comment https://forums.phpfreaks.com/topic/161482-counting-and-sum-from-different-tables/#findComment-852224 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, Link to comment https://forums.phpfreaks.com/topic/161482-counting-and-sum-from-different-tables/#findComment-852565 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. Link to comment https://forums.phpfreaks.com/topic/161482-counting-and-sum-from-different-tables/#findComment-853485 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.