Standard method for MySQL's IF() function

Question

I have found MySQL's IF() function to be very useful in giving me an efficient way to do conditional aggregate functions, like this:

SELECT SUM(IF(`something`='a', `something_weight`, 0)) AS `A`, SUM(`something_weight`) AS `All` FROM...

It is my understanding that this function is a feature of MySQL, and is not generally available in databases that use SQL.

Is there a more standard method to achieve this functionality on the database side of things?

See here for more examples: https://janac.medium.com/if-statements-in-mysql-conditional-logic-7e5acab4e8a8 — Janac Meena, Dec 03 '20 at 18:27

score 3 · Accepted Answer · answered Feb 28 '11 at 14:54

3

I'm not a sql guru but case statement

might be standard ansi.

answered Feb 28 '11 at 14:54

Nicola Cossu

Joe Stefanelli · Answer 2 · 2011-02-28T15:01:59.150

2

I believe using a CASE statement would be more standard.

SELECT SUM(CASE `something` WHEN 'a' THEN `something_weight` ELSE 0 END) AS `A`, 
       SUM(`something_weight`) AS `All` 
    FROM...

edited Feb 28 '11 at 15:01

answered Feb 28 '11 at 14:54

Joe Stefanelli

score 2 · Answer 3 · answered Mar 24 '11 at 08:34

2

CASE is the general command you can use to do conditional aggregate functions. For more info see http://www.1keydata.com/sql/sql-case.html

answered Mar 24 '11 at 08:34

Tanner

score 1 · Answer 4 · answered Feb 28 '11 at 14:54

1

In MSSQL you can do this:

SELECT SUM(CASE something WHEN 'a' THEN something_weight ELSE 0 END) as [a],
       SUM(something_weight) as [All]
FROM ...

answered Feb 28 '11 at 14:54

Johan

score 1 · Answer 5 · answered Feb 28 '11 at 14:55

1

look at using CASE: CASE (Transact-SQL)

SELECT SUM(CASE WHEN something='a' THEN XXX WHEN something='b' THEN YYY ELSE 0 END ) AS ColumnName

answered Feb 28 '11 at 14:55

RacerX

5 Answers5