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 6, 2013 2:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 215, Visits: 638
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 8, 2013 11:43 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 706, Visits: 4,508
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 8, 2013 11:56 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:56 AM
Points: 632, Visits: 1,152
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