Two counts in one sql statement

From: <dn.perl_at_gmail.com>
Date: Mon, 20 Jul 2009 08:39:02 -0700 (PDT)
Message-ID: <c816147d-992e-4071-aa06-e56fdded8d4e_at_v37g2000prg.googlegroups.com>

I have a table with 3 fields.

country state city

USA        CA     Los Angeles
USA        CA      Sacramento
USA        OR     Portland

Canada ONT Ottawa

Could I find total count for the US and counts for country-state combo with one sql statement. I doubt whether this is possible. The output should look like:
country state C/S combo Total C(ountry) count

USA        CA          2                  3   (2 in CA + 1 in OR)
USA        OR          1                  3

TIA. Received on Mon Jul 20 2009 - 10:39:02 CDT

This message: [ Message body ]
Next message: Thomas Kellerer: "Re: Two counts in one sql statement"
Previous message: ddf: "Re: Enforce constraint after design change"
Next in thread: Thomas Kellerer: "Re: Two counts in one sql statement"
Reply: Thomas Kellerer: "Re: Two counts in one sql statement"
Reply: ddf: "Re: Two counts in one sql statement"
Maybe reply: ddf: "Re: Two counts in one sql statement"
Reply: Maxim Demenko: "Re: Two counts in one sql statement"
Reply: kevin: "Re: Two counts in one sql statement"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message