SQLite count, group and order by count

Question

I have a table that looks like this:

FOO  BAR  BAZ ----+----+---- foo1 bar1 baz1 foo2 bar3 baz2 foo3 bar1 baz3 foo4 bar1 baz4 foo5 bar3 baz5 foo6 bar1 baz6 foo7 bar2 baz7 

And as a result I would like to get the count of how many times each bar appeared in the table.. so, the output I'm looking for looks like this:

BAR   COUNT -----+----- bar1    4 bar3    2 bar2    1 

Can I do a query for something like this in SQLite? I guess it should be pretty easy, but I am not an SQL programmer by any means, and I just need this simple query as a part of a python script.. Thank you.

Answer 1

SELECT foo, count(bar) FROM mytable GROUP BY bar ORDER BY count(bar) DESC; 

The group by statement tells aggregate functions to group the result set by a column. In this case "group by bar" says count the number of fields in the bar column, grouped by the different "types" of bar.

A better explanation is here: http://www.w3schools.com/sql/sql_groupby.asp