aggregates and case statements

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 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

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