> sqlite> select sic, sic_desc, state, count(*) from Companies group by > sic; > The 'group by' phrase returns one row per group, which is what I want as > long as the group is a compound of industry number and state (the > description makes it easier to read and is fixed in association with each > number).
So is this what you want? select sic, sic_desc, state, count(*) from Companies group by sic, sic_desc, state; If not explain your requirements better please. Pavel On Tue, Sep 21, 2010 at 1:47 PM, Rich Shepard <rshep...@appl-ecosys.com> wrote: > I've a table with 15 columns, including industry number, industry > description, and state. I'm trying to formulate the proper SELECT statement > to return the count of rows for each industry number/description in each of > the 5 states. I've looked at the aggregate function chapter in Rick van der > Lans' "The SQL Guide to SQLite" without seeing a suitable example. > > The closest I've come so far is: > sqlite> select sic, sic_desc, state, count(*) from Companies group by > sic; > The 'group by' phrase returns one row per group, which is what I want as > long as the group is a compound of industry number and state (the > description makes it easier to read and is fixed in association with each > number). > > What I'd like to see is a table grouped either by sic or state: > > sic sic_desc state total companies > > or > > state sic sic_desc total companies > > This shouldn't be that difficult but I'm just not thinking correctly. > > TIA, > > Rich > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users