How to order by FIELD with GROUP BY

[Marius]

I have SQL a query like this:

Code: Select all

SELECT c.type, SUM( u.account )
FROM contracts c, u_data u
WHERE c.user_id = u.id
GROUP BY c.type;

Now this gives me the desired result, but now I want to add ORDER BY to it, but by specific field name like:

Code: Select all

ORDER BY FIELD( c.type, 'type1', 'type2', ... )

But I can't insert that into the query. When I try like this:

Code: Select all

SELECT c.type, SUM( u.account )
FROM contracts c, u_data u
WHERE c.user_id = u.id
ORDER BY FIELD( c.type, 'initial', 'commision', 'overpay' )
GROUP BY c.type;

It gives my sql error:

Code: Select all

ERROR 1064 (42000): You have an error in your SQL syntax

What is the proper way in doing this?

Hi,
You just have the wrong order for GROUP BY and ORDER BY.
ORDER BY should be added after GROUP BY.

Code: Select all

...
GROUP BY c.type;
ORDER BY FIELD( c.type, 'initial', 'commision', 'overpay' )