Math in the ORDER BY clause?

[lemmin]

The following results in an error:

[...] ORDER BY MaxPositions-FilledPositions DESC

 

It would seem that you can't use math operators in the ORDER BY clause. Is there a way to get around this? I figured a function like ADD() would do the trick, but I can't find any function that would work. I realize that I could do the math as an extra SELECT item, but both MaxPositions and FilledPositions are actually aliases for pretty in depth sub-queries so they can't be used in the SELECT clause and I would have to run both of them over again with a minus sign in between to get what I want. That just isn't efficient! Any ideas?

 

Thanks

[Mchl]

What error do you get? There should be no problem doing this, as long as those names are column aliases.

[fenway]

It's your hyphen -- use backticks.

[Mchl]

I think it's supposed to be a substraction of two columns not a hyphen...

[lemmin]

Reference 'FilledPositions' not supported (reference to group function)

 

I imagine that it would work if the two values I was trying to subtract were both columns, but since they are actually alias references, it doesn't work.

 

Thanks for the help.

[Mchl]

It's not because of aliases, but because af aggregate functions. There is a workaround however: move ordering to outer query.

 

SELECT * FROM
(
  SELECT ID ,MAX(positions) AS MaxPositions, SUM(filled) AS FilledPositions FROM table GROUP BY ID
) AS t
ORDER BY MaxPositions-FilledPositions DESC;

[lemmin]

Here is the part of the query in question:

SELECT
         (SELECT COALESCE(SUM(MaxPositions),0) FROM activity_event_positions p2, activity_events e2 WHERE p2.EventID = e2.EventID AND e2.ActivityID = a.ActivityID) as MaxPositions,
         COUNT(ass.PositionID) as FilledPositions
         FROM [...]

 

I tried adding an alias to the inside of the subquery like this:

SELECT
         (SELECT COALESCE(SUM(MaxPositions),0) as m FROM activity_event_positions p2, activity_events e2 WHERE p2.EventID = e2.EventID AND e2.ActivityID = a.ActivityID) as MaxPositions,
         COUNT(ass.PositionID) as FilledPositions
         FROM [...]

 

But then,

[...]
ORDERY BY m-FilledPositions

 

Throws the error:

Unknown column 'm' in 'order clause' 

 

Did I misunderstand what you suggested?

 

Thanks for the help.

[Mchl]

Seems so.

 

SELECT * FROM
  (SELECT
         (SELECT COALESCE(SUM(MaxPositions),0) FROM activity_event_positions p2, activity_events e2 WHERE p2.EventID = e2.EventID AND e2.ActivityID = a.ActivityID) as MaxPositions,
         COUNT(ass.PositionID) as FilledPositions
         FROM [...]
  ) AS innerQuery
ORDER BY MaxPositions - FilledPositions