List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:April 26 2007 4:38pm
Subject:Re: Interesting SQL Query - Total and Grouped Counts together?
View as plain text  
Hi,

Imran Chaudhry wrote:
> I'm wondering if any of you can assist with an interesing SQL
> query.  I have a single table within a database, the relevant fields of
> which are defined as:
> 
>        CREATE TABLE tableA
>        (
>                domain text,
>                mime  text
>        );
> 
> Where "domain" is a domain, such as:
> 
> google.com
> emeraldcity.oohisntitgreen.com
> teddybears.com
> 
> An example of rows might be:
> 
> google.com, text/html
> google.com, image/gif
> google.com, image/jpeg
> google.com, text/html
> teddybears.com, text/html
> teddybears.com, image/png
> google.com, text/html
> google.com, image/png
> ...
> 
> "mime" is defined as having entries such as:
> 
> text/html
> image/png
> image/jpg
> image/gif
> application/x-tar
> 
> What I am after doing with this table is writing an SQL query which
> produces a count of all domains where the mime is equal to "text/html"
> and next to that, a total count for that domain where the mime type is
> "image/*" -- so for example, I might expect the returned resultset to
> look like:
> 
> Domain              domaincount         Mimecountimages
> ----------------       -----                       ----
> google.com        120                       12
> emeraldcity.       200                       40
> teddybears.com 11                         2
> 
> 
> So far, we've considered and tried using a same-table join, various
> group-by and rollup ideas, but am unable to come up with anything which
> will produce the above in ONE row for each domain.
> 
> Any advice and assistance would be great!
> 

Try IF or CASE expressions:

SELECT foo, count(*), sum(case when foo = 'bar' then 1 else 0 end)
FROM tbl
GROUP BY foo

Baron
Thread
Interesting SQL Query - Total and Grouped Counts together?Imran Chaudhry26 Apr
  • Re: Interesting SQL Query - Total and Grouped Counts together?Baron Schwartz26 Apr
    • Re: Interesting SQL Query - Total and Grouped Counts together?Mogens Melander26 Apr
Re: Interesting SQL Query - Total and Grouped Counts together?Imran Chaudhry30 Apr