how to take sum of count for this selct proc?

• Sivaganesh Tamilvendhan

  Hall of Fame

  Points: 3498

  More actions

  June 8, 2012 at 2:48 am

  #275642

  here i just have written select query

  select

  sub.SubCategoryId,

  sub.SubCategoryName,

  sub.CategoryId

  ,(select COUNT(1) from dbo.tblAdSpace where AdSpaceId in

  (

  select AdSpaceId from AdInfo where InfId in

  (select InfId from ApplicableCat where SubCategoryId=sub.SubCategoryId)

  )

  and ActiveStatus=1 and IsCompleted=1)SubCategorycount

  from

  tblAdSubCategory sub

  this is the output iam getting for this select query

  SubCategoryId SubCategoryName CategoryId SubCategorycount

  1 Agricultural 1 1

  2 Apartments 1 2

  3 Commercial 2 1

  4 Commercial for rent 2 0

  now i just need the count of Categorycount by suming all the SubCategorycount depend upon the CategoryId

  like this iam expecting output

  SubCategoryId SubCategoryName CategoryId SubCategorycount Categorycount

  1 Agricultural 1 1 3

  2 Apartments 1 2 3

  3 Commercial 2 1 1

  4 Commercial for rent 2 0 1

  can any one please tell me how to get the sum of count

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 8, 2012 at 3:41 am

  #1498594

  Can you please post your question with some additional information?

  You can read this quick article on how to post a question effectively[/url].

  -- Gianluca Sartori

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  June 8, 2012 at 4:21 am

  #1498617

  Something like this perhaps

  But without the table definitions and sample data like Gianluca has requested, we are clutching at straws.

  with cte as

  (

  select

  sub.SubCategoryId,

  sub.SubCategoryName,

  sub.CategoryId

  ,(select COUNT(1) from dbo.tblAdSpace where AdSpaceId in

  (

  select AdSpaceId from AdInfo where InfId in

  (select InfId from ApplicableCat where SubCategoryId=sub.SubCategoryId)

  )

  and ActiveStatus=1 and IsCompleted=1) as SubCategorycount

  from

  tblAdSubCategory sub

  )

  select *, (SELECT SUM(subcategorycount) FROM cte c2 where c1.categoryid = c2.categoryid GROUP BY c2.CategoryId) from cte c1

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  June 8, 2012 at 5:00 am

  #1498642

  I Thought there might be a way to do this with DENSE_RANK(), but i'm struggling

  MVDBA

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 8, 2012 at 5:42 am

  #1498660

  Can you write this query using JOINs instead of IN, which implies that you are unsure of the relative cardinality of the tables? Something similar to this perhaps:

  SELECT

  sub.SubCategoryId,

  sub.SubCategoryName,

  sub.CategoryId,

  SubCategorycount = ac.Counts

  FROM tblAdSubCategory sub

  CROSS APPLY (

  SELECT Counts = COUNT(1)

  FROM dbo.tblAdSpace ap

  INNER JOIN (

  SELECT DISTINCT AdSpaceId

  FROM AdInfo ai

  INNER JOIN ApplicableCat ac

  ON ac.InfId = ai.InfId

  WHERE ac.SubCategoryId = sub.SubCategoryId

  ) f ON f.AdSpaceId = ap.AdSpaceId

  WHERE ap.ActiveStatus = 1 AND ap.IsCompleted = 1

  ) ac (Counts)

  ^{“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