1

I'm trying return values 0 when field is null. For example if there is one id_articulo with importe null, the query will return 0.

TABLE

CREATE TABLE `PUJAS` (
  `ID_PUJA` int(11) NOT NULL AUTO_INCREMENT,
  `ID_ARTICULO` int(11) DEFAULT NULL,
  `ID_USUARIO` int(11) DEFAULT NULL,
  `TIEMPO` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `IMPORTE` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID_PUJA`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAU

LT CHARSET=latin1;

QUERY

SELECT IFNULL(MAX(IMPORTE),0) FROM PUJAS GROUP BY ID_ARTICULO

enter image description here

Improve this question
1
  • It'd be a lot better to paste in the actual SQL code than a screenshot of it. Commented May 14, 2013 at 0:05

3 Answers 3

Reset to default
1

You forgot 2nd argument of IFNULL() function

SELECT IFNULL(MAX(IMPORTE), 0) FROM PUJAS GROUP BY ID_ARTICULO
Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

sorry I pasted the code bad. I tried this too, and doesn't work
And SELECT MAX(IFNULL(IMPORTE, 0)) FROM PUJAS GROUP BY ID_ARTICULO ?
Too, it doesn't return 0 :(
I don't understand why don't is there any 0?
Cause you select MAX values
|
1

You could use COALESCE ():

SELECT COALESCE (MAX (Importe), 0)
FROM
  ...

that will return the first non null value from its arguments. If MAX is null it will return 0.

EDIT

Maybe you are looking for this:

SELECT
  CASE WHEN SUM(IMPORTE IS NULL)=0 THEN MAX(IMPORTE) ELSE 0 END
FROM
  PUJAS
GROUP BY
  ID_ARTICULO

This will return MAX(Importe) if there are no Importe for each ID_ARTICULO that have a null value. Otherwise it will return 0. Please see fiddle here.

Improve this answer

5 Comments

@jal it has to work... but even your query looks correct sqlfiddle.com/#!2/bd4e9/2
@jal what does it return? null or another value?
@jal do you want to return 0 if there's at least one row that has a null Importe?
for example if the table has got a id_articulo without importe, add 0
@jal i update my answer, but I'm not sure that i can understand your question correctly, can you provide some sample data on a fiddle?
0

add another ifnull:

SELECT IFNULL(MAX(IFNULL(IMPORTE, 0)), 0) FROM PUJAS GROUP BY ID_ARTICULO
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.