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