48

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.

tkit
  • 8,082
  • 6
  • 40
  • 71

1 Answers1

66
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

BartoszKP
  • 34,786
  • 15
  • 102
  • 130
Edgar Velasquez Lim
  • 2,426
  • 18
  • 15
  • 6
    almost.. but got me on the right track. thanks! here is a full solution in case someone needs it: "SELECT bar, count(bar) FROM mytable GROUP BY bar ORDER BY count(bar) DESC" – tkit Jul 19 '11 at 12:04
  • 4
    Actually, instead repeating count(bar) twice, you should use the "AS" statement: "SELECT foo, count(bar) AS tot_bar_count FROM mytable GROUP BY bar ORDER BY tot_bar_count DESC;" – Paolo Rovelli Feb 07 '14 at 08:21