gpt4 book ai didi

sql - PLSQL : Get sum for each day of week and total sum for week in a single query

转载 作者:行者123 更新时间:2023-12-04 14:10:34 27 4
gpt4 key购买 nike

假设,我有一个表 ClientTrade,如下所示:

ClientName , TradeDate , Quantity

我想在 Oracle PLSQL 中创建一个查询,它应该返回这样的结果:
(这些天数来自 TradeDate 列,Mon = sum(Quantity) for Mon ,Tue = sum(Quantity) for Tue ...等)
ClientName  Mon Tue Wed Thu Fri Sat Sun TotalForWeek
ABC 10 15 5 2 4 0 0 34
XYZ 1 1 2 1 2 0 0 7

假设此报告始终具有使其运行一周的 where 条件,是否可以在单个查询中创建此报告?

最佳答案

稍微简化一下...

SELECT ClientName, 
SUM(CASE WHEN to_char(TradeDate,'DY')='MON' THEN Quantity ELSE NULL END) AS Mon,
SUM(CASE WHEN to_char(TradeDate,'DY')='TUE' THEN Quantity ELSE NULL END) AS Tue,
SUM(CASE WHEN to_char(TradeDate,'DY')='WED' THEN Quantity ELSE NULL END) AS Wed,
SUM(CASE WHEN to_char(TradeDate,'DY')='THU' THEN Quantity ELSE NULL END) AS Thu,
SUM(CASE WHEN to_char(TradeDate,'DY')='FRI' THEN Quantity ELSE NULL END) AS Fri,
SUM(CASE WHEN to_char(TradeDate,'DY')='SAT' THEN Quantity ELSE NULL END) AS Sat,
SUM(CASE WHEN to_char(TradeDate,'DY')='SUN' THEN Quantity ELSE NULL END) AS Sun,
SUM(Quantity) AS TotalForWeek
FROM ClientTrade
GROUP BY ClientName

关于sql - PLSQL : Get sum for each day of week and total sum for week in a single query,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/775787/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com