CODEWITHSUNDEEP
pythonsqlite
maciejo0
maciejo0

Reputation: 21

Ordering a GROUP BY query

I'm using sqlite3 database in my very simple program written in python. Table I have a problem with contains three columns:

id, software_version, place

Now I would like to count the frequency of a particular software version, so I created this query:

SELECT software_ssh, COUNT (software_ssh)  FROM simple_table group by software_ssh

As a result I have got:

program_version_1.1, 2
program_version_1.2, 2
program_version_1.3, 20
program_version_2.1, 7

Unfortunately the output is not sorted by frequency. How can I fix the query to make it work better?

Upvotes: 1

Views: 34

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1122372

Add an ORDER BY on the COUNT results; give that result an alias:

SELECT
    software_ssh,
    COUNT (software_ssh) as freq
FROM simple_table
GROUP BY software_ssh 
ORDER BY freq

Use ORDER BY freq DESC if you need the rows ordered from most frequent to least.

Demo:

>>> from pprint import pprint
>>> c.execute('SELECT software_ssh, COUNT (software_ssh)  FROM simple_table group by software_ssh')
<sqlite3.Cursor object at 0x102034490>
>>> pprint(list(c))
[(u'program_version_1.1', 2),
 (u'program_version_1.2', 2),
 (u'program_version_1.3', 20),
 (u'program_version_2.1', 7)]
>>> c.execute('''
... SELECT
...     software_ssh,
...     COUNT (software_ssh) as freq
... FROM simple_table
... GROUP BY software_ssh
... ORDER BY freq
... ''')
<sqlite3.Cursor object at 0x102034490>
>>> pprint(list(c))
[(u'program_version_1.1', 2),
 (u'program_version_1.2', 2),
 (u'program_version_2.1', 7),
 (u'program_version_1.3', 20)]

Upvotes: 1

Related Questions