获得最后7天，20天和YTD计数

发布于 2025-02-04 21:18:50 字数 421 浏览 2 评论 0原文

我有一个带有列sales_date，sales_id，sales_region的表，我希望在过去7天，20天和YTD上显示销售计数。

我在下面有此查询，该查询返回了7天和20天的正确计数，但YTD显示了7和20天的计数。如何调整此查询以正确显示YTD？谢谢

select region,
case when current_date- sales_date <=7 then 'Past7'
    when current_date- sales_date <=28 then 'Past20'
    else 'YTD' 

end as "trendsales",
   count(*) as salescount
from sales_table
where sales_date >= '2022-01-01'
group by 1

原文

I have a table with columns sales_date, sales_id, sales_region and I am looking to display the count of sales for the past 7 days, 20 days and YTD.

I have this query below that returns the correct count for 7 and 20 days but the YTD shows the count minus the 7 and 20 days. How can I tweak this query to show the YTD correctly? Thank you

select region,
case when current_date- sales_date <=7 then 'Past7'
    when current_date- sales_date <=28 then 'Past20'
    else 'YTD' 

end as "trendsales",
   count(*) as salescount
from sales_table
where sales_date >= '2022-01-01'
group by 1

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

魔法唧唧 2025-02-11 21:18:50

您可以将其旋转一点。 4列区域，YTD，Past7和Past20将是列。

select region,
sum(case when current_date- sales_date <=7 then 1 else 0 end) as Past7,
sum(case when current_date- sales_date <=28 then 1 else 0 end) as Past20,
count(*) as YTD
from sales_table
where sales_date >= '2022-01-01'
group by 1

you could pivot it a bit. 4 columns Region, YTD, Past7, and Past20 would be columns.

select region,
sum(case when current_date- sales_date <=7 then 1 else 0 end) as Past7,
sum(case when current_date- sales_date <=28 then 1 else 0 end) as Past20,
count(*) as YTD
from sales_table
where sales_date >= '2022-01-01'
group by 1

回复收藏 0 原文

~没有更多了~