Warm tip: This article is reproduced from serverfault.com, please click
database oracle subquery case-when

How can I add condition for total sales per month for specific category

发布于 2020-11-28 06:44:51

Let's assume there is a table as below structure

select Year, Month , customer, Category, Amount 
from claim

I want to apply a discount for each month as shown in the table (green columns):

  • if the 'Cus X' total sales for brand and generic categories in JAN is greater than 15000, then apply a discount for only 'Brand' category of 2%
  • if it is greater than 20000 then apply a discount of 3%

and the same thing for other months and customers.

Is it possible to do that in SQL with a subquery or some functions that can help?

Please advise

enter image description here

Questioner
Maram A-zaid
Viewed
0
分享
Popeye 2020-11-28 15:35:27

You can use case .. when statement and analytical function as follows:

select Year, Month , customer, Category, Amount,
       Case when category = 'Brand'
            then
              Case when total_sales > 15000 then '2%'
                   When total_sales > 20000 then '3%'
              End
       End as disc,
       Case when category = 'Brand' 
            then
              Case when total_sales > 15000 then 2*amount/100
                   When total_sales > 20000 then 3*amount/100
              End
       End disc_amount
From
(select Year, Month , customer, Category, Amount,
        sum(case when category in ('Brand', 'Generic') then amount else 0 end)
          over (partition by year, month, customer) as total_sales
 from claim)
Maram A-zaid 2020-11-29 07:50:53

Perfect, That what I wanted exactly thank you for your help, and thank you for your comment. you are right there is no discount for the second customer

热门帖子

1
Android 开发方向：传统 View 开发 or 拥抱 Jetpack Compose
2
浙江 ISP 会屏蔽 Wireguard UDP 端口
3
最近很多小伙伴弄港卡，分享一下个人开卡经历
4
vercel 免费版 3 个指标都超了，好像我的网站已经获得了基础流量。还好套了 cloudflare 不然流量也超了。
5
最近换了真我手机，发现这系统连应用的数据都无法备份
6
MacBook Pro 决赛圈
7
连下厨房也要放弃网页版了
8
杭州岗位太少了
9
300 档预算求推荐一款 87 键机械键盘
10
求推荐硬路由（1000 内）

热门github

1
A multi-platform library for OpenGL, OpenGL ES, Vulkan, window and input
2
Dev tool that writes scalable apps from scratch while the developer oversees the implementation
3
shadcn/ui, but for Svelte. ✨
4
The Python Risk Identification Tool for generative AI (PyRIT) is an open access automation framework to empower security professionals and machine learning engineers to proactively find risks in their generative AI systems.
5
Performance-portable, length-agnostic SIMD with runtime dispatch
6
ZK Credo
7
OpenCodeInterpreter: Integrating Code Generation with Execution and Refinement
8
Joplin - the secure note taking and to-do app with synchronisation capabilities for Windows, macOS, Linux, Android and iOS.
9
Mamba is a new state space model architecture showing promising performance on information-dense data such as language modeling, where previous subquadratic models fall short of Transformers. It is based on the line of progress on structured state space models, with an efficient hardware-aware design and implementation in the spirit of FlashAttention.
10
This repository contains System Design resources which are useful while preparing for interviews and learning Distributed Systems
11
Curso para aprender el lenguaje de programación Python desde cero y para principiantes. 75 clases, 37 horas en vídeo, código, proyectos y grupo de chat. Fundamentos, frontend, backend, testing, IA...
12
🎓 Path to a free self-taught education in Computer Science!
13
1️⃣🐝🏎️ The One Billion Row Challenge -- A fun exploration of how quickly 1B rows from a text file can be aggregated with Java
14
A collective list of free APIs
15
📚 Freely available programming books