MySQL query to return number 'zero' if no

问题:

When selecting a DATE and that date does not exist in my table it currently will return an empty result set. How can I be able to return the number zero for those empty result sets instead?:

    SELECT SUM(TOTAL), SUM(5STAR), STORE, DATE
    FROM `table` WHERE DATE >= '2012-02-24' GROUP BY TOTAL

MySQL returned an empty result set (i.e. zero rows)

I want to instead return the results of the SUM(TOTAL) and SUM(5STAR) (if zero rows) as the number zero (0).

FULL TABLE STRUCTURE:

• ID = Primary
• DATE = UNIQUE (date)
• STORE
• 5STAR
• 4STAR
• 3STAR
• 2STAR
• 1STAR
• TOTAL
• FROM = UNIQUE

回答1:

I think it would be easier to handle the empty result set on the PHP side (count the returned rows). If you want to handle it in the database, you should create a stored procedure.

DELIMITER $$

CREATE PROCEDURE `mc`.`new_routine` (IN DT DATETIME)
BEGIN

IF EXISTS (SELECT 1 FROM `table` WHERE DATE >= @DT)
THEN
    SELECT SUM(TOTAL) AS SumTotal, SUM(5STAR) AS Sum5Star, STORE, `DATE`
    FROM `table`
    WHERE DATE >= @DT
    GROUP BY TOTAL;
ELSE
    SELECT 0 AS SumTotal, 0 AS Sum5Star, NULL AS STORE, NULL AS `DATE`;
END IF;

END

回答2:

Try COALESCE

SELECT COALESCE(SUM(TOTAL),0), COALESCE(SUM(5STAR),0), STORE, DATE
FROM `table` WHERE DATE >= '2012-02-24' GROUP BY TOTAL

回答3:

TRY

SELECT
       IFNULL(SUM(TOTAL), 0) AS total,
       IFNULL(SUM(5STAR), 0) AS FiveStar, 
       STORE,
       DATE
FROM `table`
WHERE DATE >= '2012-02-24'
GROUP BY TOTAL

Reference