[thelist] SQL: summing and counting the same field, based on value

[Matt Warden]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Canfield, Joel wrote:
>> select sum(end - start), count(case type when 9 then 1 else 0 end)
>> from ...
>> group by type
> 
> you and Tab both had a good idea; problem is, it still returns four
> values, it's just that three of them are now zero instead of the real
> count. I need this to return only one value: that for type = 9.

I know what you think you want, but I don't think you've thought through
exactly what the result is going to look like.

If you are going to group by type, you will have n values, where n is
the number of types. Yet the count for type=9 is only one value. There
is not a way to marry these.

However, if you have a fixed number of types, you could get away with
something like this:

select
    sum(case type when 1 then end-start else 0) as sumtype1,
    sum(case type when 2 then end-start else 0) as sumtype2,
    sum(case type when 3 then end-start else 0) as sumtype3,
    sum(case type when 4 then end-start else 0) as sumtype4,
    ...
    sum(case type when 9 then end-start else 0) as sumtype9,
    count(case type when 9 then 1 else 0 end) as counttype9
from ...


- --
Matt Warden
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEjzPprI3LObhzHRMRAo/1AJ9FcLsIsU+/PAGXDgwhhcWoLICfKwCg8XDj
lF7emqdKAey+0nkHsBpEANc=
=OHRx
-----END PGP SIGNATURE-----