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
[EMAIL PROTECTED]
http://atomdatabase.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
