'IF' in 'SELECT' statement - choose output value based on column values

Question

'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report

I need add up to be sum if report.type='P' and - sum if report.type='N'. How would I add this to the above question?

database mysql sql

4 years ago by Amit8z4mc

2 Answers

Login / Signup to Answer the Question.

Login Signup

**espadacoder11** · Answer 1 · 2021-05-20T09:30:59



SELECT id, 

       IF(type = 'P', amount, amount  -1) as amount

FROM report

Additionally, you could handle when the condition is null. In the case of a null amount:



SELECT id, 

       IF(type = 'P', IFNULL(amount,0), IFNULL(amount,0)  -1) as amount

FROM report

The part IFNULL(amount,0) means when amount is not null return amount else return 0.

**kshitijrana14** · Answer 2 · 2021-05-21T14:54:40

Use a case statement:

select id,

    case report.type

        when 'P' then amount

        when 'N' then -amount

    end as amount

from

    `report`

C TUTORIAL

C PROGRAMS

INTERVIEW TESTS

EXECUTE CODE

C++ TUTORIAL

C++ PROGRAMS

INTERVIEW TESTS

EXECUTE CODE

PYTHON TUTORIAL

PYTHON HOW TOS

INTERVIEW TESTS

EXECUTE CODE

JAVA TUTORIAL

JAVA CODE EXAMPLES

SPRING TUTORIAL

MORE IN JAVA

COMPUTER ARCHITECTURE

COMPUTER NETWORK

OPERATING SYSTEM

DBMS & SQL

PL/SQL

MongoDB

EXECUTE SQL

ANDROID DEVELOPMENT

GO LANGUAGE

LINUX

DOCKER

HTML TAGS (A to Z)

CSS REFERENCES

SASS/SCSS

KOTLIN

GAME DEVELOPMENT

PHP

GIT GUIDE

JAVASCRIPT

ADVANCED DSA

'IF' in 'SELECT' statement - choose output value based on column values

2 Answers

Login / Signup to Answer the Question.