Re: Two counts in one sql statement

From: Thomas Kellerer <OTPXDAJCSJVU_at_spammotel.com>
Date: Mon, 20 Jul 2009 17:50:55 +0200
Message-ID: <7cji30F284negU1_at_mid.individual.net>

dn.perl_at_gmail.com, 20.07.2009 17:39:
> 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
>

Something like

SELECT country,

       state
       count(*) over (partition by country, state) as combo_count,
       count(*) over (partition by country) as country_count

FROM info_table Received on Mon Jul 20 2009 - 10:50:55 CDT