oracle sql每周统计每个月的天数

bbmckpt7 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(261)

我有这两张table（时间和销售额）：
次

TIME_ID   |   DAY_NAME  |  DAY_NUMBER_IN_WEEK    |  CALENDAR_MONTH_NAME |  CALENDAR_MONTH_ID
1998-01-10|   Monday    |  1                     |  January             |  1684
1998-01-10|   Tuesday   |  2                     |  January             |  1684
1998-01-10|   Wednesday |  3                     |  January             |  1684
...
1998-01-11|   Monday    |  1                     |  February            |  1685
1998-01-11|   Tuesday   |  2                     |  January             |  1685
1998-01-11|   Wednesday |  3                     |  January             |  1685

出售

PROD_ID   |  TIME_ID     |  AMOUNT_SOLD
13        |  1998-01-10  |  1232          
13        |  1998-01-11  |  1233 
14        |  1998-01-11  |  1233

我需要为一周中的每一天（星期一，星期二，星期三…）和每个月的每一天每个产品id的销售金额总和列。

SELECT SUM(times.day_number_in_week), times.calendar_month_name, times.day_name, times.calendar_year
FROM sales
INNER JOIN times  ON times.time_id = sales.time_id
GROUP BY times.calendar_month_number, times.calendar_month_name, times.day_name, times.calendar_year

输出：

5988    March   Wednesday   1998
9408    April   Thursday    1998
7532    June    Sunday  1998
9220    July    Thursday    1998
7490    July    Sunday  1998
12540   August  Saturday    1998

但是这个金额所有的星期三为所有年份，我需要的金额为1个月的所有天（星期三，星期一…）为一个月。
你能帮助我吗？

sql Join group-by sum oracle

来源：https://stackoverflow.com/questions/62479799/oracle-sql-count-days-in-week-for-every-month

1条答案

按热度按时间

mw3dktmi1#

您可以执行条件聚合：

SELECT 
    t.calendar_year
    t.calendar_month_name, 
    SUM(case when t.day_number_in_week = 1 then s.amount_sold else 0 end) amount_sold_mon,
    SUM(case when t.day_number_in_week = 2 then s.amount_sold else 0 end) amount_sold_tue,
    SUM(case when t.day_number_in_week = 3 then s.amount_sold else 0 end) amount_sold_wed,
    ...
FROM sales s
INNER JOIN times t ON t.time_id = sales.time_id
GROUP BY t.calendar_year, t.calendar_month_number, t.calendar_month_name

赞(0）回复(0）举报 2021-07-26

我来回答

oracle sql每周统计每个月的天数

1条答案

相关问题

热门标签

最新问答