Re: [sqlite] Syntax for Counting Rows By Multiple Groups

[Pavel Ivanov]

>        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  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

Re: [sqlite] Syntax for Counting Rows By Multiple Groups

[Igor Tandetnik]

Rich Shepard  wrote:
>   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

Just throw more columns into GROUP BY clause:

select sic, sic_desc, state, count(*) from Companies group by sic, sic_desc, 
state;

-- 
Igor Tandetnik


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Syntax for Counting Rows By Multiple Groups

[Rich Shepard]

   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_descstate   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