How to order by FIELD with GROUP BY

Discuss coding issues, and scripts related to PHP and MySQL.
Marius
Posts: 107

How to order by FIELD with GROUP BY

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?

Admin Posts: 805
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' )