Hi, I have a question

Have

PERFDATA Table

DAY PROG ENV CPU
01 P1 DEV 52
01 P1 DEV 52
02 P2 DEV 40
03 P1 PRD 20
07 P1 PRD 10
03 P2 PRD 33
02 P3 PRD 75
06 P3 PRD 10
05 P3 QA 91
07 P7 DEV 15

I need to show on columns sumarized

PROG CPU-DEV CPU-QA CPU-PRD
P1 104 0 30
P2 40 0 33
P3 0 91 85
P7 15 0 0

My best aproach is

SELECT PROG,
(CASE WHEN ENV = 'DEV' THEN sum(CPU) ELSE 0 END) AS CPU-DEV,
(CASE WHEN ENV = 'QA' THEN sum(CPU) ELSE 0 END) AS CPU-QA,
(CASE WHEN ENV = 'PRD' THEN sum(CPU) ELSE 0 END) AS CPU-PRD
from PERFDATA
group by PROG, ENV

but got

PROG CPU-DEV CPU-QA CPU-PRD
P1 104 0 0
P1 0 0 30
P2 40 0 0
P2 0 0 33
P3 0 91 0
P3 0 0 85
P7 15 0 0

Any idea ?

Thanks !!

Put CASE expressions inside SUM functions, like
SUM(CASE WHEN ENV = 'DEV' THEN CPU ELSE 0 END) AS CPU-DEV

and remove ENV from GROUP BY clause, like
GROUP BY prog

It Worked !!!

Thanks!!


"Tonkuma" <tonkuma (AT) fiberbit (DOT) net> escribió en el mensaje
news:dfa68469-3ddc-44d6-ae81-b99361205136 (AT) z1g2000prf (DOT) googlegroups.com...