SELECT
SUBSTR(A.INV_NO,1,3) "TYPE",
sum(decode(TO_CHAR(a.INV_dtime,'MON'),'JAN',SUM(B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT),0)) "JAN",
sum(decode(TO_CHAR(a.INV_dtime,'MON'),'FEB',SUM(B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT),0)) "FEB",
sum(decode(TO_CHAR(a.INV_dtime,'MON'),'MAR',SUM(B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT),0)) "MAR"
FROM MSS_INV_HDR A,MSS_INV_LINES B,MSS_RO_HDR C
WHERE
c.ROST_CODE like '%SER%'
AND a.sts IN('I','S','P')
AND A.inv_dtime BETWEEN TO_DATE('01-JAN-07 00:00:00', 'dd-mon-yy hh24:mi:ss')
AND TO_DATE('30-NOV-07 23:59:59', 'dd-mon-yy hh24:mi:ss')
AND A.ID=B.INHD_ID
AND a.ROHD_ID=c.ID
AND C.MAKE_CODE IN ('T7','O7')
GROUP BY TO_CHAR(A.inv_dtime,'MON'),SUBSTR(A.INV_NO,1,3);
When I run getting the error ORA-00937 not a single-group group function
sorry, i have say wrong for my first reply.
If you use "select colA, colB, colC from Tb group by ..."
then the colA, colB, colC must all in group by's column,
or colA, colB, colC must use cluster functions, such as sum,max.
by the way, the code in your attachment have 2 mistakes:
1.the table name become INV_HDR,INV_LINES,RO_HDR,
not like your tip's(MSS_INV_HDR,MSS_INV_LINES,MSS_RO_HDR)
2.the table RO_HDR doesn't have column which called ROST_CODE,
so i ignore the column in conditions.
anyway, your sql is not bad, only to experice more, to study oracle function deeply.
the following sql is the answer.
Code:
SELECT SUBSTR(A.INV_NO,1,3) "TYPE",
sum(decode(TO_CHAR(a.INV_dtime,'MON'),'JAN',B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT,0)) "JAN",
sum(decode(TO_CHAR(a.INV_dtime,'MON'),'FEB',B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT,0)) "FEB"
FROM INV_HDR A,INV_LINES B,RO_HDR C
WHERE --c.ROST_CODE like '%SER%'
--AND
a.sts IN('I','S','P')
AND A.inv_dtime BETWEEN TO_DATE('01-01-07 00:00:00', 'dd-mon-yy hh24:mi:ss')
AND TO_DATE('30-11-07 23:59:59', 'dd-mon-yy hh24:mi:ss')
AND A.ID=B.INHD_ID
AND a.ROHD_ID=c.ID
AND C.MAKE_CODE IN ('T7','O7')
GROUP BY TO_CHAR(A.inv_dtime,'MON'),SUBSTR(A.INV_NO,1,3)
Last edited by anyoneokay; 11-30-2007 at 03:27 AM.
Sorry, I found some repeat types in my result.
So you must used sum 2 times, the second time for outside
select t.type, sum(t.jan), sum(t.feb) from
(
SELECT SUBSTR(A.INV_NO,1,3) "TYPE",
sum(decode(TO_CHAR(a.INV_dtime,'MON'),'JAN',B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT,0)) "JAN",
sum(decode(TO_CHAR(a.INV_dtime,'MON'),'FEB',B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT,0)) "FEB"
FROM INV_HDR A,INV_LINES B,RO_HDR C
WHERE --c.ROST_CODE like '%SER%'
--AND
a.sts IN('I','S','P')
AND A.inv_dtime BETWEEN TO_DATE('01-01-07 00:00:00', 'dd-mon-yy hh24:mi:ss')
AND TO_DATE('30-11-07 23:59:59', 'dd-mon-yy hh24:mi:ss')
AND A.ID=B.INHD_ID
AND a.ROHD_ID=c.ID
AND C.MAKE_CODE IN ('T7','O7')
GROUP BY TO_CHAR(A.inv_dtime,'MON'),SUBSTR(A.INV_NO,1,3)
) t
group by t.type;