Обсуждение: 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