MySQL Multiple COUNT group

Question

I have this query but I don't know if it's possible to do this. Basically, I'm doing a count and group by each column. However, I want to show the count DESC/ASC. Since they're different columns, I can I achieve this? Here is my query:

SELECT flags.ID, flags.COMMENT_ID, flags.CONTENT_ID, flags.USER_ID, flags.WALLPOST_ID, flags.FLAGGED_BY, flags.DATE_FLAGGED, flags.PROD_ID, flags.STAGE_ID, COUNT(flags.COMMENT_ID) AS comment_count, COUNT(flags.CONTENT_ID) AS content_count, COUNT(flags.WALLPOST_ID) AS wall_count, COUNT(flags.USER_ID) AS user_count FROM flags LEFT JOIN comment ON (flags.COMMENT_ID=comment.ID) LEFT JOIN content ON (flags.CONTENT_ID=content.ID) LEFT JOIN sf_guard_user_profile ON (flags.USER_ID=sf_guard_user_profile.ID) LEFT JOIN wallpost ON (flags.WALLPOST_ID=wallpost.ID) GROUP BY flags.COMMENT_ID,flags.CONTENT_ID,flags.USER_ID,flags.WALLPOST_ID ORDER BY comment_count DESC,content_count DESC,wall_count DESC,user_count DESC LIMIT 1000

Basically, if you look at the results below, I want to be able to group the count into a single column. That way, I can do pagination and sort easily.

I'm new StackOverflow user so I cannot post an image. Here is this link: http://i40.tinypic.com/2wbvxci.jpg

Thanks in advance!

Answer 1

Looks from that jpg that only one of the counts will be non-zero for any given row, so changing the SELECT to have, instead of the

COUNT(flags.COMMENT_ID) AS comment_count, COUNT(flags.CONTENT_ID) AS content_count, COUNT(flags.WALLPOST_ID) AS wall_count, COUNT(flags.USER_ID) AS user_count

part, an expression such as

(COUNT(flags.COMMENT_ID) + COUNT(flags.CONTENT_ID) + COUNT(flags.WALLPOST_ID) + COUNT(flags.USER_ID)) AS total_count

might be what you want.