Thread: aggregates and case statements

Is there a way to make the following statement return the total of all effort. At the moment it gives a list of sum vs case.

select sum(effort),

CASE WHEN effortandattainment.effort=5 THEN -3

WHEN effortandattainment.effort=4 THEN -2

WHEN effortandattainment.effort=3 THEN 1

WHEN effortandattainment.effort=2 THEN 2

WHEN effortandattainment.effort=1 THEN 3

END

from effortandattainment

group by case

Regards

Garry

Garry Saddington wrote:
> Is there a way to make the following statement return the total of all effort. At the moment it gives a list of sum
vscase. 
>
>
> select sum(effort),
> CASE WHEN effortandattainment.effort=5 THEN -3
> WHEN effortandattainment.effort=4 THEN -2
> WHEN effortandattainment.effort=3 THEN 1
> WHEN effortandattainment.effort=2 THEN 2
> WHEN effortandattainment.effort=1 THEN 3
>
>        END
> from effortandattainment

SELECT sum(effort) FROM effortandattainment;

But I'm guessing that's not what you mean. Can you show what output
you'd like to have?

--
  Richard Huxton
  Archonet Ltd

garry@schoolteachers.co.uk wrote:
> Quoting Richard Huxton <dev@archonet.com>:
>> But I'm guessing that's not what you mean. Can you show what output
>> you'd like to have?
>
> effort sum
>  1     245
>  2     463
> etc.
>
> Each value for effort has a different statistical meaning as in the case
> statement.

Sorry - still don't understand. The query you originally gave should
have provided the sum for each case (which you seem to be calling
"effort" here. I don't see where the problem is.

--
  Richard Huxton
  Archonet Ltd