December 6, 2013 at 2:12 pm
I am trying to get the average for number of Ids per day
But , some how I get "0".
Can anyone suggest
With CTE1
As
(
SELECT
count([ID]) as No_OF_IDS,
Days
FROM [Proddata]
LEFT OUTER JOIN SupplyData as S ON Proddata.Group = S.Group
GroupBy Days
)
Select Sum(ISNULL(No_of_IDS,0))/Sum(ISNULL(Days,0))
from CTE1
December 8, 2013 at 11:43 pm
How about:
SELECT x.Days, AVG(x.No_Of_IDs) As AvgCount
FROM
(SELECT Days,
count([ID]) as No_OF_IDS
FROM [Proddata]
LEFT OUTER JOIN SupplyData as S ON Proddata.Group = S.Group
GroupBy Days
) x
GROUP BY x.Days;
No CTE required.
December 8, 2013 at 11:56 pm
sharonsql2013 (12/6/2013)
I am trying to get the average for number of Ids per dayBut , some how I get "0".
Can anyone suggest
It giving zero because there might be not data matching against the join you are using, try to change the query to the inner join to check if there any results you find, 2ndly there is not need to CTE, desire result can be achieve through a simple query.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply