テーブルに入場、退場、未のステータスがあるとします。
入場済み、入場中、退場済み、総来場組数の件数を出したいのですが
どのようなsqlをつくればよいですか
lang
1SELECT TO_CHAR(to_date,'yyyy/mm/dd') as date 2 ,extract(dow from to_date) 3 ,(array['(日)','(月)','(火)','(水)','(木)','(金)','(土)'])[extract(dow from to_date) + 1] as days 4 ,count(*) FROM ( 5 SELECT e_id,to_date(come_date,'yyyy/mm/dd'),COUNT(*) 6 FROM $table_come 7 GROUP BY e_id,to_date(come_date,'yyyy/mm/dd') 8 ) as t1 9 10 GROUP BY to_date 11 ORDER BY date 12
前回作ったのは入退場全部含めて一括で表示していました
lang
1SELECT e_id,to_date(come_date,'yyyy/mm/dd'), 2SUM(CASE WHEN come_place='入場' THEN 1 END) AS 入場済み, 3SUM(CASE WHEN come_place='退場' THEN 1 END) AS 退場済み, 4 5SUM(1) 6 FROM table 7 GROUP BY e_id,to_date(come_date,'yyyy/mm/dd') 8 ORDER BY e_id
case whenを使いましたが入場中をどのように書けばよいかわかりません
入場中とは、入場記録があるが同日の退場記録がない状態です
追記
いろいろやってみて
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 6 ,SUM( 7 SUM(CASE WHEN in1 > 1 AND out1 != 0 THEN 1 END ) + SUM(CASE WHEN out1 > 0 THEN 1 END ) 8 ) as "総来場組数" 9 FROM ( 10 SELECT 11 TO_CHAR(to_date,'yyyy/mm/dd') as date 12 ,extract(dow from to_date) 13 ,(array['(日)','(月)','(火)','(水)','(木)','(金)','(土)'])[extract(dow from to_date) + 1] as days 14 ,SUM(CASE WHEN in1 > 0 THEN 1 END) as "来場済み" 15 ,SUM(CASE WHEN out1 > 0 THEN 1 END ) as "退場済み" 16 ,SUM(CASE WHEN in1 > 1 AND out1 != 0 THEN 1 END ) as "来場中" 17 FROM ( 18 SELECT e_id,to_date(come_date,'yyyy/mm/dd'), 19 SUM(CASE WHEN come_place='来場' THEN 1 ELSE 0 END) AS in1 20 ,SUM(CASE WHEN come_place='退場' THEN 1 ELSE 0 END) AS out1 21 FROM table 22 GROUP BY e_id,to_date(come_date,'yyyy/mm/dd') 23 )as t2 24 ) as t1 25GROUP BY to_date 26ORDER BY date
ERROR: 列"t2.to_date"はGROUP BY句で出現しなければならないか、集約関数内で使用しなければなりません
LINE 2: TO_CHAR(to_date,'yyyy/mm/dd') as date
となります。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。