Re: Interesting SQL Query - Total and Grouped Counts together?
Baron, Thanks very much for that simple but very effective solution. I altered your SQL slightly, the final SQL looks like this: SELECT domain, count(*) AS 'count all', SUM(IF(mime = 'text/html', 1, 0)) AS 'count text', SUM(IF(mime LIKE 'image/%', 1, 0)) AS 'count image' FROM tableA GROUP BY domain ORDER BY domain Thanks again, Imran Chaudhry -- 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]
Interesting SQL Query - Total and Grouped Counts together?
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.com120 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]
Re: Interesting SQL Query - Total and Grouped Counts together?
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.com120 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]
Re: Interesting SQL Query - Total and Grouped Counts together?
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; domainmime -- '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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
