CODEWITHSUNDEEP
mysqlrow
Sohel Mansuri
Sohel Mansuri

Reputation: 564

MySQL Query Not returning all rows with this query

I have the following query: 

SELECT 
  IF(COUNT(*) > 0, COUNT(*), '0') AS Found, 
  IF(NWLat, NWLat, '0')           AS NWLat, 
  IF(NWLon, NWLon, '0')           AS NWLon, 
  IF(SELat, SELat, '0')           AS SELat, 
  IF(SELon, SELon, '0')           AS SELon 
FROM s 
WHERE s.ci = '4' 
  AND s.snf = 'Ch'
GROUP BY s.sid

Right now it returns rows for matched query. However, when nothing is matched, it returns nothing. How can I make it return "Found: 0, NWLat: 0, NWLon: 0, SELat: 0, SELon: 0" if nothing is found?

Upvotes: 1

Views: 781

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

It is better, not to do so in MySQL. SQL is not about that. From your front end application if your query doesn't return any records, then you can easily print 0's.

However, if you need to do this any way you can do this:

SELECT 
  IF(COUNT(*) > 0, COUNT(*), '0') AS Found, 
  IF(NWLat, NWLat, '0')           AS NWLat, 
  IF(NWLon, NWLon, '0')           AS NWLon, 
  IF(SELat, SELat, '0')           AS SELat, 
  IF(SELon, SELon, '0')           AS SELon 
FROM s 
WHERE s.ci = '4' 
  AND s.snf = 'Ch'
GROUP BY s.sid 
UNION ALL
SELECT
  '0' AS Found, 
  '0' AS NWLat, 
  '0' AS NWLon, 
  '0' AS SELat, 
  '0' AS SELon 
FROM s 
WHERE NOT EXISTS(
    SELECT 
      IF(COUNT(*) > 0, COUNT(*), '0') AS Found, 
      IF(NWLat, NWLat, '0')           AS NWLat, 
      IF(NWLon, NWLon, '0')           AS NWLon, 
      IF(SELat, SELat, '0')           AS SELat, 
      IF(SELon, SELon, '0')           AS SELon 
    FROM s 
    WHERE s.ci = '4' 
    AND s.snf = 'Ch'
    GROUP BY s.sid
);

Demos:

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

For you last comment, do you want to try this? here you con't have to count all to determine if you have the records that you need based on the condition.

SELECT     
case when NWLat is null then 0 else NWLat end AS NWLat, 
case when NWLon is null then 0 else NWLon end AS NWLon,
case when SELat is null then 0 else SELat end AS SELat,
case when SELon is null then 0 else SELon end AS SELon
FROM S
where NOT EXISTS(SELECT 1
             FROM s
             WHERE ci = 4 and snf = 'Ch')
GROUP BY sid, NWLon, SELat, SELon;

Upvotes: 0

DWright
DWright

Reputation: 9500

FYI, if you genuinely have aCOUNT(*) Of 0, this means that there are NO rows for that group, so there will never be a row in the result. Your IF(COUNT(*) > 0, COUNT(*), '0') won't do you any good, because there will be no row in which to show a 0.

As to the other ifs, I suspect you want something like this:
SELECT (case when NWLat is null then 0 else NWLat end)
. . .
GROUP BY sid, (case when NWLat is null then 0 else NWLat end), etc.

This will allow those to be included in the group and will allow 0 to show as a group.

Upvotes: 0

Related Questions