Re: Interesting SQL Query - Total and Grouped Counts together?

[Mogens Melander]

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
[phone]
[phone]



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