Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CTE to find average Expand / Collapse
Author
Message
Posted Friday, December 06, 2013 2:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 170, Visits: 509
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
Post #1520747
Posted Sunday, December 08, 2013 11:43 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 579, Visits: 3,782
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.
Post #1520918
Posted Sunday, December 08, 2013 11:56 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:06 AM
Points: 482, Visits: 841
sharonsql2013 (12/6/2013)
I am trying to get the average for number of Ids per day

But , 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.
Post #1520923
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse