13

I am attempting to run a query using MAX() but my issue is that if the field contains a NULL value the data returns nothing. Updating the table is not an option, so how should this query be re-written to return 0 when NULL?

Sample DDL below - and I hardcoded a WHERE clause that returns nothing just for illustrative purposes, ideally I want to return all usernames and the highest logincount for the user

CREATE TABLE Test 
(
 username varchar(50) not null,
 logincount int,
 logindate Date
);

Insert Into Test (username, logincount, logindate) 
VALUES
('er11', 1, '2017-01-01'), ('er11', 2, '2017-01-02'), ('ff12', NULL, NULL)
,('jb88', 1, '2017-01-09');

With maxlogincount As
(
    select max(logincount) As "MaxLoginCount"
    ,username
    FROM Test
    GROUP BY username
)
Select
username
,logincount
,logindate
FROM Test t
inner join maxlogincount mlc
ON t.username = mlc.username
And mlc."MaxLoginCount" = t.logincount
WHERE t.username = 'ff12'
Order by t.username asc

2 Answers 2

19

Use max(coalesce(logincount, 0)) to avoid NULLs

According to Postgres docs (9.6):

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:

6
  • Where would I need to add the COALESCE()? I tried to wrap it in the CTE - but still returns nothing Feb 1, 2017 at 16:07
  • 1
    max(coalesce(logincount,0)) try it here: rextester.com/LSLFL11968
    – McNets
    Feb 1, 2017 at 16:11
  • And all in a one-liner with no cte, very nice! Feb 1, 2017 at 16:12
  • 2
    This will still return NULL if the no rows match the WHERE clause. If that is not wanted, use coalesce(max(logincount),0) instead.
    – jjanes
    Feb 1, 2017 at 16:40
  • @jjanes do you mean table Test has no rows?
    – McNets
    Feb 1, 2017 at 16:44
2

Maybe I've missed something obvious here, but wouldn't the NULLIF function be a much better (and more elegant) solution?

Postgres documentation here.

SELECT
  COUNT(NULLIF(logincount,0)) AS "Max Login Count",
  username,
FROM Test
GROUP BY username
ORDER BY "Max Login Count";

Result:

'Max Login Count'   username
----------------------------
               0        ff12
               1        jb88
               2        er11
7
  • 1
    NULLIF does (kind of) the opposite of COALESCE. It will return NULL when logicount is 0. Feb 1, 2017 at 20:57
  • Insert Into Test (username, logincount, logindate) VALUES ('xyz', 0, NULL); - this produces a correct result - i.e. 'Max Login Count' = 0 - or, again, have I missed something?
    – Vérace
    Feb 1, 2017 at 21:03
  • The sample data in the question are ('ff12', NULL, NULL). What does NULLIF give in that case? Feb 1, 2017 at 21:08
  • It gives 0 as (I) expected.
    – Vérace
    Feb 1, 2017 at 21:10
  • Oh your code has COUNT and doesn't have MAX, I didn't notice that. It will work for these cases though. But count is different than MAX. I'm not if that's what the OP wants. Feb 1, 2017 at 21:14

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.