InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 18:48:04 GMT20http://www.sqlservercentral.com/Forums/Topic1407824-391-1.aspx[quote][b]Steve Jones - SSC Editor (1/16/2013)[/b][hr]This kind of works, but I'm wondering if you've calculated something in this view already.[code="sql"]SELECT cid, COUNT(DISTINCT ccid) FROM dbo.MyTable GROUP BY cid[/code][/quote]I think this should be fine ..If not please post the ddl's .I am unable to understand the problem..this is how I tried , le me know it works for you..[code="sql"]CREATE TABLE #test ( CID INT, CCID CHAR(10), CCOUNT INT )INSERT INTO #testVALUES (2,'a',1), (2,'b',1)-- Edited this partSELECT *,(SELECT SUM(ccount) FROM(SELECT CCOUNT FROM #test GROUP BY CID,CCOUNT)abc)CountTotalFROM #test[/code]Thu, 17 Jan 2013 01:32:47 GMTdemonfoxhttp://www.sqlservercentral.com/Forums/Topic1407824-391-1.aspx[quote][b]dwain.c (1/16/2013)[/b][hr]... Are you sure you're not part Navaho?[/quote]No but one of my programming buddies went AWOL for six months last year - and resurfaced in Michigan married to an Apache!Thu, 17 Jan 2013 01:11:11 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1407824-391-1.aspx[quote][b]sabeer.mvit (1/16/2013)[/b][hr]The ccount is not obtain directly from table as done here.Am rather using the View written as belowWith 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 countfrom table ch inner join table bon ch.id=b.id[/quote]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.Thu, 17 Jan 2013 01:09:48 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1407824-391-1.aspxThe ccount is not obtain directly from table as done here.Am rather using the View written as belowWith 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 countfrom table ch inner join table bon ch.id=b.idWed, 16 Jan 2013 23:30:26 GMTsabeer.mvithttp://www.sqlservercentral.com/Forums/Topic1407824-391-1.aspx[quote][b]ChrisM@Work (1/16/2013)[/b][hr][code="sql"];WITH SampleData AS ( SELECT CId = 1, CCId = 'a', CCount = 3, totalCount = 6 UNION ALL SELECT 1, 'a', 3, 6 UNION ALL SELECT 1, 'b', 3, 6 UNION ALL SELECT 1, 'c', 3, 6 UNION ALL SELECT 2, 'b', 2, 6 UNION ALL SELECT 2, 'b', 2, 6 UNION ALL SELECT 2, 'a', 2, 6 UNION ALL SELECT 2, 'a', 2, 6 UNION ALL SELECT 3, 'v', 1, 6) SELECT CId, CCId, CCount, totalCount = SUM([First]) OVER (PARTITION BY (SELECT NULL))FROM ( SELECT *, [First] = CASE WHEN 1 = ROW_NUMBER() OVER(PARTITION BY CId ORDER BY CCId) THEN CCount END FROM SampleData) d[/code][/quote]+1Chris - You are a true code-talker! Are you sure you're not part Navaho?Wed, 16 Jan 2013 17:42:29 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1407824-391-1.aspxWell the query that Chris posted returns the exact output you specified. If you need something different you have to explain it. We can't see your screen, we are not familiar with your project and we don't know the business rules.Wed, 16 Jan 2013 12:04:02 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1407824-391-1.aspxAs per my requirement , it would be something :SELECT CId, CCId, CCount, (select totalCount = SUM([First]) OVER (PARTITION BY (SELECT NULL))FROM ( SELECT *, [First] = CASE WHEN 1 = ROW_NUMBER() OVER(PARTITION BY CId ORDER BY CCId) THEN CCount END FROM SampleData) d)from SampleDatabut it's giving error.Wed, 16 Jan 2013 11:08:36 GMTsabeer.mvithttp://www.sqlservercentral.com/Forums/Topic1407824-391-1.aspx[code="sql"];WITH SampleData AS ( SELECT CId = 1, CCId = 'a', CCount = 3, totalCount = 6 UNION ALL SELECT 1, 'a', 3, 6 UNION ALL SELECT 1, 'b', 3, 6 UNION ALL SELECT 1, 'c', 3, 6 UNION ALL SELECT 2, 'b', 2, 6 UNION ALL SELECT 2, 'b', 2, 6 UNION ALL SELECT 2, 'a', 2, 6 UNION ALL SELECT 2, 'a', 2, 6 UNION ALL SELECT 3, 'v', 1, 6) SELECT CId, CCId, CCount, totalCount = SUM([First]) OVER (PARTITION BY (SELECT NULL))FROM ( SELECT *, [First] = CASE WHEN 1 = ROW_NUMBER() OVER(PARTITION BY CId ORDER BY CCId) THEN CCount END FROM SampleData) d[/code]Wed, 16 Jan 2013 09:22:49 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1407824-391-1.aspxThis kind of works, but I'm wondering if you've calculated something in this view already.[code="sql"]SELECT cid, COUNT(DISTINCT ccid) FROM dbo.MyTable GROUP BY cid[/code]Wed, 16 Jan 2013 08:55:27 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1407824-391-1.aspxHow are you getting the counts? are you basing this on the CCID column for a CID?It's better if you can give DDL like this:[code="sql"]CREATE TABLE mytable( CId int, CCId varchar(10), CCount int, totalCount int);GOINSERT MyTable VALUES (1, 'a',3, 6), (1, 'a', 3, 6), (1, 'b', 3, 6), (1, 'c', 3, 6), (2, 'b', 2, 6), (2, 'b', 2, 6), (2, 'a', 2, 6), (2, 'a', 2, 6), (3, 'v', 1, 6);go[/code]Also, please explain the logic, don't just give some results and expect someone to interpret them to match your business rule. It saves time.Wed, 16 Jan 2013 08:54:56 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1407824-391-1.aspxThe View obtains the first three columns , i need to add one more column(totalCount) to the view that obtains the totalCountCId CCId CCount totalCount1 a 3 61 a 3 61 b 3 61 c 3 62 b 2 62 b 2 62 a 2 62 a 2 63 v 1 6How to get the totalCount as 6 ?(Business rule for Cid=1 Ccount=3 Cid=2 Ccount=2 Cid=3 Ccount=1So the totalCount =3+2+1 =6)Wed, 16 Jan 2013 07:08:28 GMTsabeer.mvit