EchoFool Posted September 23, 2011 Share Posted September 23, 2011 Hey, I have a query which loads events and i want to order them by total number of fans of that event. The number of fans is based on how many people are a fan of the event which is stored in a secondary table for example: Event Table Event ID | Event Name Event Fans UserID | Event ID How do i do it ? Quote Link to comment https://forums.phpfreaks.com/topic/247736-order-data-by-total-number-of-rows/ Share on other sites More sharing options...
Maq Posted September 23, 2011 Share Posted September 23, 2011 Join the tables on EventID, SUM the UserID and ORDER BY the sum alias. Quote Link to comment https://forums.phpfreaks.com/topic/247736-order-data-by-total-number-of-rows/#findComment-1272148 Share on other sites More sharing options...
EchoFool Posted September 23, 2011 Author Share Posted September 23, 2011 If i SUM won't that only create one row result similar to count? or is it : <?php SUM(t2.userid) AS totalfans FROM eventtable t1 LEFT JOIN eventfans t2 ON t1.eventid=t2.eventid ORDER BY totalfans ?> Quote Link to comment https://forums.phpfreaks.com/topic/247736-order-data-by-total-number-of-rows/#findComment-1272151 Share on other sites More sharing options...
Maq Posted September 23, 2011 Share Posted September 23, 2011 Yeah sorry. Not sure if this is legal with joins, but try: SELECT COUNT(t2.userid) AS t, eventname, FROM eventtable t1 LEFT JOIN eventfans t2 ON t1.eventid=t2.eventid GROUP BY t2.eventid ORDER BY t Quote Link to comment https://forums.phpfreaks.com/topic/247736-order-data-by-total-number-of-rows/#findComment-1272153 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.