List:General Discussion« Previous MessageNext Message »
From:Imran Chaudhry Date:April 26 2007 4:34pm
Subject:Interesting SQL Query - Total and Grouped Counts together?
View as plain text  
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!

-- 
Atom Database
A Custom Database Designed for Your Business
info@stripped
http://atomdatabase.com
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