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@atomdatabase.com
http://atomdatabase.com

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

On Thu, April 26, 2007 18:38, Baron Schwartz wrote:
> 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
>
> Try IF or CASE expressions:
>
> SELECT foo, count(*), sum(case when foo = 'bar' then 1 else 0 end)
> FROM tbl
> GROUP BY foo
>
> Baron

Cool, it's actually working

I've been looking for something like that before.

SELECT * FROM tablea t order by domain,mime;

domain mime
------------------------------
'google.com', 'image/gif'
'google.com', 'image/jpeg'
'google.com', 'image/png'
'google.com', 'text/html'
'google.com', 'text/html'
'google.com', 'text/html'
'teddybears.com', 'image/png'
'teddybears.com', 'text/html'

SELECT domain, count(*) `all`,
sum(case when mime = 'text/html' then 1 else 0 end) html,
sum(case when mime like 'image/%' then 1 else 0 end) image
FROM tablea
GROUP BY domain;

domain all html image
---------------------------------
'google.com', 6, 3, 3
'teddybears.com', 2, 1, 1

--
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.