CODEWITHSUNDEEP
mysqlsqlmariadb
Deniz
Deniz

Reputation: 107

sql get zero as value if no records been found

I have a simple question i'm looking around but cant have it work for me. I have made an query with current date and lets say tomorow is tuesday but there is no data in it yet because it is the future. And there can't be a record in it yet.

When i run the query it simply shows nothing only 2 colums called total | Date It does what I say. But now I want it to show me 0 instead of noting but only 2 colums.

I did search for COALESCE but for some reason it did not work it also give me no errors.

SELECT
    SUM(totalExcl) AS total, 
    DATE_FORMAT(date_add, '%W') AS 'Date'  
FROM
    ex.ps_ox_quotation 
WHERE 
    WEEK(date_add) = WEEK (UTC_TIMESTAMP()) 
        AND saleType IN ('IEW' , 'A', 'Project')
        AND DAYOFWEEK(date_add) % 6 > 4 
GROUP BY Date 
ORDER BY 'Date' DESC

What I have(table) =

| |total | Date

What I want(table) =

| |total | Date
     0     Tuesday

Today is Wednesday so it is current date and this is what it shows (btw this is current) i'm just showing it:

| |total | Date
    500    Wednesday

Upvotes: 1

Views: 424

Answers (2)

denny
denny

Reputation: 2254

Try this

SELECT
IF(SUM(totalExcl) is NULL,0,SUM(totalExcl)) AS total, 
DAYNAME(date_add) AS 'Date'  
FROM
ex.ps_ox_quotation 
WHERE 
WEEK(date_add) = WEEK (UTC_TIMESTAMP()) 
    AND saleType IN ('IEW' , 'A', 'Project')
    AND DAYOFWEEK(date_add) % 6 > 4 
GROUP BY Date 
ORDER BY 'Date' DESC

or you can do this way 

SELECT
IF(SUM(totalExcl) is NULL,0,SUM(totalExcl)) AS total, 
IF(DATE_FORMAT(date_add, '%W') is null,DAYNAME(now()),DATE_FORMAT(date_add, '%W')) AS 'Date'  
FROM
ex.ps_ox_quotation 
WHERE 
WEEK(date_add) = WEEK (UTC_TIMESTAMP()) 
    AND saleType IN ('IEW' , 'A', 'Project')
    AND DAYOFWEEK(date_add) % 6 > 4 
GROUP BY Date 
ORDER BY 'Date' DESC

Upvotes: 1

Arockia Nirmal
Arockia Nirmal

Reputation: 777

Try with IFNULL...

SELECT
    IFNULL(SUM(totalExcl),0) AS total, 
    DATE_FORMAT(date_add, '%W') AS 'Date'  
FROM
    ex.ps_ox_quotation 
WHERE 
    WEEK(date_add) = WEEK (UTC_TIMESTAMP()) 
        AND saleType IN ('IEW' , 'A', 'Project')
        AND DAYOFWEEK(date_add) % 6 > 4 
GROUP BY Date 
ORDER BY 'Date' DESC

Upvotes: 0

Related Questions