RE: How to obtain Sum of count

SSC Guru

Points: 186127

January 17, 2013 at 1:09 am

sabeer.mvit (1/16/2013)
The ccount is not obtain directly from table as done here.Am rather using the View written as below
With compTable(cid,ccid,ccount)
as
AS
(
select parentid,childrenId,COUNT(cmpcd) FROM
group by parentid,childrenId,cmpcd
)
select decsr,
pnumber,
(SELECT count(ccount) FROM compTable where parentid=ch.cn_id) as [Count] ,--[this is ccount]
-- here i need the total count
from table ch inner join table b
on ch.id=b.id

Can you post the whole query referencing the view, and also the view definition, please? Some sample data would help too - exactly what will probably depend upon the view definition.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden