Question 20280 in Mysql Question posted in
The official MySQL documentation can be found here.

Unable to order by a sub_query result – Mysql

I am trying to set up a Datatable table using serverside processing but I can’t make my order by statement work.

The returned table shows a list of courses and then the registration count for different registration statuses. Example:

SELECT 
  id, 
  name, 
  town, 
  start_date, 
  (SELECT COUNT(id) 
   FROM registration r 
   WHERE r.course = c.id AND r.status = 'REG') as reg_count, 
  (SELECT COUNT(id) 
   FROM registration r 
   WHERE r.course = c.id AND r.status = 'CAN') as can_count 
FROM courses c

When I want to order by reg_count I can’t just add ORDER BY reg_count and if I try and add the ORDER BY inside the sub_query it returns a random order. I’m sure it’s possible but can’t quite figure it out.

Thanks

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The problem wasn't with the query but with my MySQL class I use, which was calling another query and adding the order by clause to it when it's not required.


  2. 0

    I guess LeftJoin Like This may help you..

    SELECT 
  id, 
  name, 
  town, 
  start_date ,
  total_reg.reg_count,
  total_can.can_count
FROM 
  courses c
LEFT JOIN (select COUNT(id) as reg_count , course FROM registration WHERE status = 'REG' GROUP BY course) as total_reg ON total_reg.course = c.course 
LEFT JOIN (select COUNT(id) as reg_count , course FROM registration WHERE status = 'CAN' GROUP BY course) as total_reg ON total_reg.course = c.course
ORDER BY total_reg.reg_count;

    ;

    You may require to modify GROUP by column according to your result..

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search