CODEWITHSUNDEEP
mysqldatabase
Armin
Armin

Reputation: 2562

How to make query return 0 instead of empty set if there is no result

How can i make this query to return a row with 0 value if there is no value for each date

SELECT COUNT(id) FROM `panel_messages` WHERE `sent_by` = 'root' 
        AND `send_date` IN ("1395-4-25","1395-4-24","1395-4-23","1395-4-22","1395-4-21","1395-4-20","1395-4-19")
        GROUP BY `send_date`
        ORDER BY `send_date` DESC

My expected result is 7 rows like this :

| row1 |

| row2 |

| row3 |

| row4 |

| row5 |

| row6 |

| row7 |

and if there is no result for one of the rows i want it to be 0 which is default value :

| 2 |

| 0 |

| 0 |

| 2 |

| 0 |

| 3 |

| 1 |

But right now i just get 4 rows because if there is no result my query doesn't return anything :

| 2 |

| 2 |

| 3 |

| 1 |

SQL fiddle : http://sqlfiddle.com/#!9/a07486/3

Upvotes: 1

Views: 3343

Answers (3)

Charif DZ
Charif DZ

Reputation: 14721

onother answer what you are trying to do is impossible without the union :

but you can try some think else create a temporary table that contain your date 

create Table temporary (
         send_date date
   );
  insert INTO temporay("1395-4-25"),("1395-4-24"),("1395-4-23"),("1395-4-22"),("1395-4-21"),("1395-4-20"),("1395-4-19")

than do select with rigth join between your table and this one now you will have record for the date that don't have send_by 

panel_messages.sent_by | panel_messages.send_date | temporary.send_date
root                         "1395-4-25"              "1395-4-25"
root                         "1395-4-25"              "1395-4-25"
null                         null                     "1395-4-24" 
null                         null                     "1395-4-23"
root                         "1395-4-19"              "1395-4-19"
.
.
.

now you count how much message in every day all i did is create a result that can return what you need :

Try this select after you create the temporary table 

SELECT  temporary.send_date, count(sender_by)
from panel_messages RIGTH JOIN temporary ON  (temporary.send_date = panel_messages.send_date)
where 
panel_messages.sent_by like 'root'
group by  temporary.send_date
ORDER BY send_date DESC;

Upvotes: 1

1000111
1000111

Reputation: 13519

Please give it a try:

SELECT 
 COALESCE(YT.total,t.total) AS cnt
FROM 
(SELECT 0 AS total) t
LEFT JOIN
(
    SELECT 
        COUNT(id) AS total 
    FROM `panel_messages` 
    WHERE `sent_by` = 'root' 
    AND `send_date` IN ("1395-4-25","1395-4-24","1395-4-23","1395-4-22","1395-4-21","1395-4-20","1395-4-19")
    GROUP BY `send_date`
    ORDER BY `send_date` DESC
) YT 
ON 1=1;

Note:

A dummy row has been created with value 0.

Later doing a LEFT JOIN between this dummy table and your query

And finally using COALESCE you can achieve the default count 0 if your main query doesn't return anything.

EDIT:

Query:

SELECT 
COALESCE(YT.count,0) AS count
FROM
(
    SELECT ADDDATE('1395-01-01', INTERVAL @i:=@i+1 DAY) AS DAY
    FROM (
    SELECT a.a
    FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
JOIN (SELECT @i := -1) r1
) dateTable
LEFT JOIN 

(
    SELECT
        send_date,
        COUNT(id) AS count
    FROM
        `panel_messages`
    WHERE
        `sent_by` = 'root'
    AND `send_date` IN (
        "1395-4-25",
        "1395-4-24",
        "1395-4-23",
        "1395-4-20"
    )
    GROUP BY
        `send_date`
    ORDER BY
        `send_date` DESC
) AS YT 
ON dateTable.DAY = YT.send_date
WHERE dateTable.DAY IN ('1395-04-25','1395-04-24','1395-04-23','1395-04-20');

In order to get zero count for the dates which don't exist you need to create a temporary table where all the dates (under a certain range) reside.

Then making a left join between the date field of this temporary table and send_date field of your table would do the job done almost.

Finally you need to use COALESCE to get 0 if the count is NULL.

WORKING DEMO

Upvotes: 2

Charif DZ
Charif DZ

Reputation: 14721

try this :

SELECT sent_by ,"1395-4-25" as `SEND DATE`,COUNT(*)  FROM `panel_messages` WHERE `sent_by` = 'root' AND `send_date` = "1395-4-25"
 union 
SELECT sent_by ,"1395-4-24" as `SEND DATE`,COUNT(*) FROM `panel_messages` WHERE `sent_by` = 'root' AND `send_date` = "1395-4-24" 
union
SELECT sent_by ,"1395-4-23" as `SEND DATE`,COUNT(*) FROM `panel_messages` WHERE `sent_by` = 'root' AND `send_date` = "1395-4-23" 
ORDER BY  `SEND DATE` DESC

in this case when date is not found the count(*) return 0; but in the first return null add the 4 select statement and it will return 7 rows now it work but it can be better if i found onother solution i'm going back here

Upvotes: 1

Related Questions