6

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
13

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:

  • Where would I need to add the COALESCE()? I tried to wrap it in the CTE - but still returns nothing – Michael Mormon Feb 1 '17 at 16:07
  • 1
    max(coalesce(logincount,0)) try it here: rextester.com/LSLFL11968 – McNets Feb 1 '17 at 16:11
  • And all in a one-liner with no cte, very nice! – Michael Mormon Feb 1 '17 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 '17 at 16:40
  • @jjanes do you mean table Test has no rows? – McNets Feb 1 '17 at 16:44
0

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
  • NULLIF does (kind of) the opposite of COALESCE. It will return NULL when logicount is 0. – ypercubeᵀᴹ Feb 1 '17 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 '17 at 21:03
  • The sample data in the question are ('ff12', NULL, NULL). What does NULLIF give in that case? – ypercubeᵀᴹ Feb 1 '17 at 21:08
  • It gives 0 as (I) expected. – Vérace Feb 1 '17 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. – ypercubeᵀᴹ Feb 1 '17 at 21:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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