SQL Sum with Condition

SQL sum with condition

Try this instead:

SUM(CASE WHEN ValueDate > @startMonthDate THEN cash ELSE 0 END)

Explanation

Your CASE expression has incorrect syntax. It seems you are confusing the simple CASE expression syntax with the searched CASE expression syntax. See the documentation for CASE:

The CASE expression has two formats:

• The simple CASE expression compares an expression to a set of simple expressions to determine the result.
• The searched CASE expression evaluates a set of Boolean expressions to determine the result.

You want the searched CASE expression syntax:

CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

---

As a side note, if performance is an issue you may find that this expression runs more quickly if you rewrite using a JOIN and GROUP BY instead of using a dependent subquery.

How to SQL SUM with condition

AS simple as my sqlfiddle shows:

select
sum(CASE WHEN type = 'IncomingTransfer' THEN amount ELSE -amount END) as totalsum
from t;

SQL : SELECT SUM WHERE CONDITION

No need for a self-join or complex logic, you can just use conditional aggregation, which consists in using conditional expression within aggregate functions.

In MySQL, you could go:

select 
    sum(is_radiant * total_time_taken) time_a,
    sum((1 - is_radiant) * total_time_taken) time_b,
    sum(total_time_taken) time_ab
from matchpickban
where is_radiant in (0, 1)

This works because is_radiant is made of 0/1 values only - so this simplifies the logic. A more canonical way to phrase the conditional sums would be:

sum(case when is_radiant = 1 then total_time_taken else 0 end) time_a,
sum(case when is_radiant = 0 then total_time_taken else 0 end) time_b,

Select sum and average from table with different condition

Here is one possible approach (NOTE: I have no access to a PostgreSQL to check syntax, so I'm presenting here a sort of pseudo-code without checking it).

SELECT SUM(UNITS)                        AS UNITS_SUM          , 
       SUM(UNIT_PRICE)/POSS_UNITS_COUNT  AS AVERAGE_UNIT_PRICE
  FROM 
   (SELECT UNITS                       AS UNITS              ,
           CASE WHEN UNITS > 0 THEN 1
                ELSE                0
           END                         AS POSS_UNITS_COUNT   ,
           CASE WHEN UNITS > 0 THEN UNIT_PRICE
                ELSE                0
           END                         AS UNIT_PRICE
    FROM Your_Table) A ;

Note that this would yield a DIVIDE BY 0 error if no positive price is in your table (I'm assuming that such condition cannot exist; if it does, you should condition the division using a CASE where POSS_UNITS_COUNT must be > 0.

SQL: sum rows with conditions

Conditional aggregation is the way to go here, but your logic is a bit off. Also, you want to aggregate by only the date and account.

SELECT date, account,
       SUM(CASE WHEN size = 'buy' THEN size ELSE -size END) AS volume
FROM yourTable
GROUP BY date, account
ORDER BY date, account;

mysql query error: SQL sum with condition

One obvious error in your query is case where instead of case when.

In addition:

• The GROUP BY columns should be consistent with the unaggregated SELECT expressions.
• Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.
• Use table aliases and qualify column references.

So, this might work:

select bt.assignedstfid, s.staffname, s.staffsymbol, 
       sum(case when bookingstarttime > '2021-03-31'
                then timestampdiff(minute, bt.bookingstarttime, bt.bookingendtime) else 0
           end) as lengthofworking
from crm.bookingtable bt join
     crm.staffinfo s 
     on s.staffid = bt.assignedstfid 
group by bt.assignedstfid, s.staffname, s.staffsymbol ;

Sum with conditions in Snowflake

Something like this?

select customeremail, sum(total_paid)
from test
where customeremail IN (select customeremail from test where active =true )
group by customeremail;

---

Related Topics