Select Query with Case Condition and Sum()

SELECT query with CASE condition and SUM()

Select SUM(CASE When CPayment='Cash' Then CAmount Else 0 End ) as CashPaymentAmount,
       SUM(CASE When CPayment='Check' Then CAmount Else 0 End ) as CheckPaymentAmount
from TableOrderPayment
Where ( CPayment='Cash' Or CPayment='Check' ) AND CDate<=SYSDATETIME() and CStatus='Active';

How Can I SELECT query with CASE condition and SUM()

You could use ABS() to get the absolute (positive) value for the sake of making your subtraction simple:

SUM(CASE WHEN trx_type='LD' THEN ABS(LoanHist.principal) ELSE 0 END)

SQL SELECT CASE WHEN SUM

You need nested aggregation:

select -- then count the number of rows per bucket
   bucket,
   count(*)
from 
 ( -- aggregate the duration per customer first
   select customerid, 
          case
            when sum(duration) between 0 and 5 then '0-5'
            when sum(duration) between 5 and 10 then '5-10'
            else '10+'
        end as bucket
   from table
   group by customerid
 ) as dt
group by bucket

sql nested sum (case when) or select query

I suspect the syntax you want is:

select CATEGORY,
       sum(case when CATEGORY = '1' and TIME > 30 then TIME - 30 
                when CATEGORY = '2' and TIME > 90 then TIME - 90
                when CATEGORY = '3' and TIME > 365 then TIME - 365
                else 0
           end) as output
from database.table
group by CATEGORY

SQL Query with CASE inside SUM not working

You have a column alias in the SUM() expression. You just want:

SUM(CASE WHEN t.TranType = 'SRT'  THEN CAST(ROUND(t.Net, 2) AS decimal(18,2))  * -1
         WHEN t.TranType = 'DIV'  THEN t.net  * 0
         ELSE CAST(ROUND(t.Net, 2) AS decimal(18,2)) 
     END) OVER () AS Total

I'm not sure that the CAST() has the effect that you want. If you want the result as DECIMAL(18, 2), then cast after the SUM():

CAST(SUM(CASE WHEN t.TranType = 'SRT' THEN - t.Net
              WHEN t.TranType = 'DIV' THEN 0
              ELSE t.Net
          END) OVER () AS DECIMAL(18, 2)

You might want to cast before doing the sum, but for an accurate sum you should only cast afterwards.

SUM using CASE WHEN SELECT

Without going into techniques to get around being able to SELECT inside an aggregate function. Let's step back and rethink your query all together. You are trying to solve it by aggregating the Lookup table but the aggregation is really on the Employee table with a join from the lookup table.

It also appears that you are trying to do conditional aggregation to PIVOT your result. But in either event if you do the following you will get the result you specify if you do the following:

SELECT
    l.loc
    ,COUNT(DISTINCT e.Id) as EmployeesAtStaffSite1
FROM
    Lookup l
    INNER JOIN Employees e
    ON e.loc = l.loc
    AND e.pos = l.pos
WHERE
    l.lbl = 'Staff Site 1'
GROUP BY
    l.loc

Also note that if you change INNER JOIN to LEFT JOIN you can get 0 count for any locations that are staff site 1 that there are no employees assigned.

SQL SUM a CASE statement with an OVER clause

Unless you have an aggregation query (which you don't), this should work:

SUM(CASE WHEN ReceiptORIssueIndicator = '+'
         THEN NetQuantity ELSE 0
    END) OVER (PARTITION BY SnapshotWeek, Plant, MaterialNumber ORDER BY Calendar.WeekSort) AS CumulativeSupplyQuantity,

It is a bit hard to figure out what you really want. Your outer query has no aggregation, so it is not clear that aggregation is needed. In a query with aggregation:

SUM(SUM(CASE WHEN ReceiptORIssueIndicator = '+'
             THEN NetQuantity ELSE 0
        END)
   ) OVER (PARTITION BY SnapshotWeek, Plant, MaterialNumber ORDER BY Calendar.WeekSort) AS CumulativeSupplyQuantity,

This assumes that all the PARTITION BY and ORDER BY columns are GROUP BY keys. Otherwise, you need to use aggregation functions for them.

Use GROUP BY with SELECT CASE WHEN statement

I think you want to phrase this with the CASE as an argument to the SUM():

SELECT ORDER_ID,
       SUM(CASE WHEN ORDER_DATE BETWEEN '2020-07-01' AND '2020-12-31'
                THEN ORDER_AMOUNT * 1.16
                ELSE ORDER_AMOUNT * 1.19 
           END) AS gross_amount
FROM my_dwh_table
GROUP BY ORDER_ID;

No ORDER BY is needed.

Note: This assumes that ORDER_DATE is never NULL (hence no need for an ELSE 0 and that '2020-07-01' is a valid date on your system. I would normally use DATE '2020-07-01' for a date constant.

---

Related Topics