CODEWITHSUNDEEP
mysql
kinson jiang
kinson jiang

Reputation: 1

SQL sum from different columns

I have bids table with two bids price columns bidsprice1 and bidsprice2. and all users have bidsprice1 amount, and some users also have bidsprice2 amount.

I want to calculate sum(bidsprice2) based on bidsprice2, if some users have null bidsprice2, then based on its bidsprice1.

How to write this sum query?

Upvotes: 0

Views: 57

Answers (2)

tunerscafe
tunerscafe

Reputation: 91

SUM(CASE WHEN bidsprice2 IS NULL THEN bidsprice1 ELSE bidsprice2 END)

In MySQL

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270599

Use COALESCE() to return the first non-NULL argument. That can be wrapped directly inside the aggregate SUM(). This has the advantage of being able to add additional columns if the need arises.

SELECT
  /* SUM() based on bidsprice2 if non-null, bidsprice1, or finally 0 if both NULL */
  SUM(COALESCE(bidsprice2, bidsprice1, 0)) AS your_sum
FROM yourtable

Upvotes: 1

Related Questions