Sandun Tharaka Sandun Tharaka - 7 months ago 26
SQL Question

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.

Answer

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.
Comments