lang
1SELECT 2 TO_CHAR(to_date,'yyyy/mm/dd') as date 3 ,extract(dow from to_date) 4 ,(array['(日)','(月)','(火)','(水)','(木)','(金)','(土)'])[extract(dow from to_date) + 1] as days 5 ,t1.raizyou 6 ,t1.taizai 7 ,t1.taizyou 8 ,SUM( 9 COALESCE(t1.taizai,0)+COALESCE(t1.taizyou,0) 10 ) as souraizyou 11 ,SUM( 12 COALESCE(t1.taizai,0)+COALESCE(t1.taizyou,0) 13 ) as total 14 15 FROM ( 16 SELECT 17 to_date 18 ,SUM(CASE WHEN in1 > 0 THEN 1 END) as raizyou 19 ,SUM(CASE WHEN in1 >= 1 AND out1 = 0 THEN 1 END ) as taizai 20 ,SUM(CASE WHEN out1 > 0 THEN 1 END ) as taizyou 21 22 FROM ( 23 SELECT e_id,to_date(come_date,'yyyy/mm/dd'), 24 SUM(CASE WHEN come_place='OK' THEN 1 ELSE 0 END) AS in1 25 ,SUM(CASE WHEN come_place='NG' THEN 1 ELSE 0 END) AS out1 26 FROM table 27 GROUP BY e_id,to_date(come_date,'yyyy/mm/dd') 28 ORDER BY to_date 29 )as t2 30 GROUP BY to_date 31 ORDER BY to_date 32 ) as t1 33GROUP BY to_date,raizyou,taizai,taizyou 34ORDER BY date
上記のSQLでは
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
|date | ~ | ~ | total |
|2015/05/19| 12 | 2 | 1000 |
|2015/05/20| 23 | 2 | 2000 |
となるのですが
全日程の合計を出したい場合はどうすればよいですか?
この場合は3000の値です
副問い合わせをまたネストしていけばよろしいですか
他に簡単な方法があれば教えてください
回答2件
あなたの回答
tips
プレビュー