CODEWITHSUNDEEP
mysqlsqljoinnullsubquery
bemzoo
bemzoo

Reputation: 172

mySQL how to display NULL as 0

Trying to get my code to show 0 rather than NULL when no data is present:

SELECT e.EntryName AS Name, e.EntryDate AS Date,

(SELECT COALESCE(COUNT(l.crashNumber), 0)
FROM Crash l
WHERE l.crashName = e.EntryName 
AND l.crashDate = e.EntryDate
GROUP BY l.crashNumber
ORDER BY COUNT(l.crashNumber) DESC
LIMIT 1)

FROM 
(SELECT e.EntryName, e.EntryDate
FROM Entry e
GROUP BY e.EntryName, e.EntryDate)

e;

As you can see I've tried using COALESCE, I've tried using ISNULL. The idea is that not all races have crashes so their output is displayed as NULL, though I wish for when it is NULL it should display 0.

Upvotes: 2

Views: 91

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Use:

SELECT e.raceEntryRaceName AS raceName, e.raceEntryRaceDate AS raceDate,

Coalesce( ( SELECT COUNT(l.pitstopRaceNumber)
           FROM PitStops l
           WHERE l.pitstopRaceName = e.raceEntryRaceName 
             AND l.pitstopRaceDate = e.raceEntryRaceDate
           GROUP BY l.pitstopRaceNumber
           ORDER BY COUNT(l.pitstopRaceNumber) DESC
           LIMIT 1), 0) AS mostPitstops

FROM .....

Upvotes: 3

Related Questions