急求一SQL语句的解决方法~~~~
已知SQL语句~：
SELECT * FROM ( SELECT TRUNC(((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)/4*4) Begdate,
TRUNC( ((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)/4*4+3 ) Enddate,
COUNT (Vendor) Unckqty FROM table1 WHERE (Cfm_status=0)
GROUP BY ((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)/4*4,
((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)/4*4+3)
WHERE Begdate> =24 ORDER BY Begdate
可得到如下数据：
Begdate Enddate Unckqty
24 27 1
24 27 1
24 27 2
24 27 4
24 27 3
25 28 1
25 28 3
25 28 3
要得到如下结果：
Begdate Enddate Unckqty
24 27 11
25 28 7
请问该如何修改上面的SQL语句
[解决办法]
SELECT Begdate,Enddate, sum(Unckqty) as Unckqty from (
SELECT * FROM ( SELECT TRUNC(((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)/4*4) Begdate,
TRUNC( ((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)/4*4+3 ) Enddate,
COUNT (Vendor) Unckqty FROM table1 WHERE (Cfm_status=0)
GROUP BY ((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)/4*4,
((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)/4*4+3)
WHERE Begdate> =24 )
group by Begdate,Enddate
ORDER BY Begdate