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