Search code examples
sqlsql-server-ce

Where clause not select o count in in SQL Server CE

I have a problem in select the all count value list to table using SQL CE.

SELECT  Time AS Hour,Cast (COUNT(ReceivedTime)*1.0/(10)as decimal(16,2)) AS HourlyTotal 
    FROM tblTime 
    LEFT OUTER JOIN tblMessageReceived ON Time = ReceivedTime  
    WHERE ReceivedDateTime >= '2016-01-01'  AND ReceivedDateTime <= '2016-07-07' 
    GROUP BY Time

But it select the non zero count only. As the result table below.

Result Table

In here i want to select all Hour time with the 0 count values.

Solution

  • Your WHERE clause effectively turns your LEFT JOIN into an INNER JOIN. The solution is to put them in the ON clause:

    SELECT  
    t.Time AS Hour,
    CAST(COUNT(t.ReceivedTime)*1.0/(10) AS DECIMAL(16, 2)) AS HourlyTotal 
FROM tblTime t
LEFT OUTER JOIN tblMessageReceived tmr
    ON t.Time = tmr.ReceivedTime  
    AND tmr.ReceivedDateTime >= '2016-01-01'
    AND tmr.ReceivedDateTime <= '2016-07-07' 
GROUP BY t.Time

    Note:

    • Use a meaningful alias to improve readability.