CODEWITHSUNDEEP
mysqlsqlselect
Ammar Hayder Khan
Ammar Hayder Khan

Reputation: 1335

Get the 0 value if No result in column -- MySQL

I have the following query 

SELECT COUNT( iContactId ) AS Users, DATE( dCreatedAt ) AS ActivityDate 
FROM contacts WHERE iAppId =".$iAppId." 
AND DATE(dCreatedAt) IN (CURRENT_DATE(), CURRENT_DATE()-1 ) 
GROUP BY ActivityDate

by this i am getting 

Users |ActivityDate
1     |2014-09-19

i want 0 if there is no match rows

e.g 

Users |ActivityDate
0     |CURRENT_DATE()
0     | CURRENT_DATE()-1

How can i do that.

Upvotes: 0

Views: 81

Answers (4)

Gervs
Gervs

Reputation: 1397

In most answers this is used voor date substraction:

SELECT CURRENT_DATE() - 1
-> 20140919

This will result in impliciet type casting and date wil be casted to int, use this instead:

SELECT CURRENT_DATE - INTERVAL 1 DAY
-> 2014-09-19

My solution

SELECT
    d.ActivityDate,
    COUNT(c.iContactId) Users
FROM
    (
    SELECT CURRENT_DATE - INTERVAL 1 DAY ActivityDate FROM DUAL
    UNION
    SELECT CURRENT_DATE FROM DUAL
    ) d
LEFT JOIN
    contacts c
    ON DATE(c.dCreatedAt) = d.ActivityDate
    AND c.iAppId = " . $iAppId . "
GROUP BY d.ActivityDate

Upvotes: 0

Abhi
Abhi

Reputation: 334

QUERY

SELECT
COUNT(C.iContactId) AS Users,
DATE(C.dCreatedAt) AS ActivityDate 
FROM
contacts C
    LEFT OUTER JOIN
(
    SELECT CURRENT_DATE() AS Dates FROM dual
        UNION
    SELECT CURRENT_DATE() - 1 AS Dates FROM dual
) D
    ON
        D.Dates = DATE(C.dCreatedAt)
WHERE
C.iAppId =".$iAppId."
GROUP BY
C.ActivityDate

Upvotes: 1

pln
pln

Reputation: 1198

You could use union

SELECT COUNT( iContactId ) AS Users, CURRENT_DATE() AS ActivityDate
FROM contacts WHERE iAppId =".$iAppId." 
AND DATE(dCreatedAt) = CURRENT_DATE()
UNION
SELECT COUNT( iContactId ) AS Users, CURRENT_DATE() - 1 AS ActivityDate 
FROM contacts WHERE iAppId =".$iAppId." 
AND DATE(dCreatedAt) = CURRENT_DATE() - 1

Upvotes: 0

Tony Wu
Tony Wu

Reputation: 1107

QUERY

SELECT 
ISNULL( iContactId ,0) AS Users, 
DATE( dCreatedAt ) AS ActivityDate 
FROM contacts 
WHERE iAppId =".$iAppId." 
AND DATE(dCreatedAt) IN (CURRENT_DATE(), CURRENT_DATE()-1 ) 
GROUP BY ActivityDate

Upvotes: 0

Related Questions