mysql order by count - ordering by value

Question

I am attempting to count number of records in a database by grouping them. This works fine, but when I try to order by, it orders the count by a different method than wanted. Example Result:

Question - Answer - Count

Q1 - A1 - 1

Q2 - A2 - 11

Q3 - A3 - 2

Result wanted: I want 11 after 2-9, not before. The query is simply:

SELECT Question, Answer, count(*) as `Count` GROUP BY Question, Answer ORDER BY Question, Answer

A further example of the sort is that the mysql sorts like, 1,11,118,12,2,3 where I am expecting the increasing value like 1,2,3,11,12,118

Mattia Caputo · Accepted Answer · 2015-10-12 15:34:26Z

1

try this query

SELECT Question, Answer, count(*) as `Count`
FROM table
GROUP BY Question, Answer
ORDER BY count(*) ASC

answered Oct 12, 2015 at 15:34

Mattia Caputo

9691 gold badge8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt · Accepted Answer · 2015-10-12 15:33:44Z

0

You have put in your query

ORDER BY Question, Answer

If you want 11 to come after 2, then surely you want

ORDER BY Count

answered Oct 12, 2015 at 15:33

Matt

3,3736 gold badges34 silver badges55 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:27:04Z

0

The issue appears that I was trying to order by character value instead of integer value? I have to cast the answer as an integer and then it order properly. Here is the query that works:

SELECT Question, Answer, count(*) as `Count` GROUP BY Question, Answer ORDER BY Question, CAST( answer AS SIGNED INTEGER )

Found the answer here: Sorting varchar field numerically in MySQL

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Oct 12, 2015 at 16:07

nwolybug

4825 silver badges13 bronze badges

Collectives™ on Stack Overflow

mysql order by count - ordering by value

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related