postgresqlでgroup byしたクエリをgroup byしたい

Question

メール送信履歴を管理するテーブルで、メール1通につき、1レコードが作成されます。

このテーブルから店舗ごとの日次件数を取得したいのですが、方法がわかりません。

お教えいただけますでしょうか？

email_history

id numeric not null primary_key
shop_id numeric
message text
created_date timestamp

shop_id numeric not null primary_key
name text

試したこと

with s as(select count(*), shop_id, created_date from email_history group by shopid)
select count(*) from s group by created_date;

出現したエラー

column "created_date" must appear in the group by clause or be used in an aggregate function

created_dateをgroup byに含めてくださいと言っているのはわかったのですが
どのようにしたらよいのかわからず、お詳しい方、お教えくださいm(_ _)m

Comments

[takanaweb5]

created_dateはtimestamp型ですが時刻情報を持っていないのですか？
実質date型として使用しているのですか？
時刻情報を持っているのであれば、日付単位にまとめるgroup byが必要と思われます。

Answer 1

SELECT
  shop_id
, created_date::date 
, count(*) as 件数
FROM email_history
GROUP BY
  shop_id
, created_date::date

created_dateはtimestamp型なので日付単位にまとめています

Answer 2

created_dateをgroup byに含めてくださいと言っているのはわかったのですが

まずは、select count(*), shop_id, created_date from email_history group by shopid を動くようにしてみてはいかがかと。

Comments

[nikoniko]

まずは、店舗ごとの件数の取得方法ありがとうございます。日次と店舗両方で絞った件数の取り方が知りたく、困っております。もう少し調べてみます！

Answer 3

with s as(select count(*), shop_id, created_date from email_history group by shopid)
select count(*) from s group by created_date;

↓

with s as (
  select count(*), shop_id, created_date from email_history group by shopid, created_date
)
select count(*) from s group by created_date;

sの定義の中のgroup by句にないものは、集約関数count()と並べられないかと思います。

Comments

[nikoniko]

コメントありがとうございます！
なるほど、group by句にcreated_dateがないから怒られていたのですね…。
ただ、sをshop_idとcreated_dateでgroup byしてしまうと、

[RECORD 1]
count | 1
[RECORD 2]
count | 1
[RECORD 3]
count | 1
………

のように全部1件になってしまいます。

私の元々のsqlがわるかったのですが
こんなイメージで取得したいです。

日にちでSUMしないといけないのかな…

[RECORD 1]
shopid | 1
created_date | 2021-12-01
count | 100

[RECORD 2]
shopid | 1
created_date | 2021-12-02
count | 10

[RECORD 3]
shopid | 3
created_date | 2021-12-01
count | 20

[RECORD 3]
shopid | 3
created_date | 2021-12-02
count | 10

[shiketa]

> のように全部1件になってしまいます。

`select count(*) from s group by created_date`を`select * from s`とすればいい。つまり、with句を使う必要も意味もない、ということ。

[退会済みユーザー]

サンプルデータもない中で想像を巡らせるのは難しいですね。
第三者が再現しやすいよう、サンプルデータとともに、手計算によるほしい結果の形もあると、良いかと思います。