Sum from a SubQuery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sum from a SubQuery

Author

Topic

fshuja
Starting Member

7 Posts

Posted - 2007-12-01 : 01:24:37

how can i calculate sum for the Counts column
the query is like this

SELECT C1.ID, C1.Name, Count(*)as Counts
FROM ClassifiedsView_Ads AS CA
INNER JOIN Classifieds_Categories AS C1 ON CA.CategoryId = C1.ID
INNER JOIN Classifieds_Members AS M ON CA.MemberID = M.ID
WHERE
(AdStatus = 100) AND M.AspNetUsername= 'abc'
GROUP BY C1.ID, C1.Name

this query is returning me a number of records and for each record its count. I want to return only the sum of all these counts.

thnks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-01 : 01:29:05

[code]SELECT SUM(Counts)
FROM
(
SELECT C1.ID, C1.Name, COUNT(*)AS Counts
FROM ClassifiedsView_Ads AS CA
INNER JOIN Classifieds_Categories AS C1 ON CA.CategoryId = C1.ID
INNER JOIN Classifieds_Members AS M ON CA.MemberID = M.ID
WHERE AdStatus = 100
AND M.AspNetUsername = 'abc'
GROUP BY C1.ID, C1.Name
)a[/code]

KH
[spoiler]Time is always against us[/spoiler]

fshuja
Starting Member

7 Posts

Posted - 2007-12-01 : 04:53:13

thnks a lot it works.
why we need variable a at end??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-01 : 04:57:10

that is not a variable but table alias for the derived table

KH
[spoiler]Time is always against us[/spoiler]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-12-03 : 06:52:04

quote:
Originally posted by khtan

SELECT SUM(Counts)
FROM
(
SELECT C1.ID, C1.Name, COUNT(*)AS Counts
FROM ClassifiedsView_Ads AS CA
INNER JOIN Classifieds_Categories AS C1 ON CA.CategoryId = C1.ID
INNER JOIN Classifieds_Members AS M ON CA.MemberID = M.ID
WHERE AdStatus = 100
AND M.AspNetUsername = 'abc'
GROUP BY C1.ID, C1.Name
)a

KH
[spoiler]Time is always against us[/spoiler]
khtan
If we just add rollup instead of using the derived table wont work?
I mean
SELECT C1.ID, C1.Name, Count(*)as Counts
FROM ClassifiedsView_Ads AS CA
INNER JOIN Classifieds_Categories AS C1 ON CA.CategoryId = C1.ID
INNER JOIN Classifieds_Members AS M ON CA.MemberID = M.ID
WHERE
(AdStatus = 100) AND M.AspNetUsername= 'abc'
GROUP BY C1.ID, C1.Name with Rollup

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-03 : 08:01:58

quote:
Originally posted by ayamas

quote:
Originally posted by khtan

SELECT SUM(Counts)
FROM
(
SELECT C1.ID, C1.Name, COUNT(*)AS Counts
FROM ClassifiedsView_Ads AS CA
INNER JOIN Classifieds_Categories AS C1 ON CA.CategoryId = C1.ID
INNER JOIN Classifieds_Members AS M ON CA.MemberID = M.ID
WHERE AdStatus = 100
AND M.AspNetUsername = 'abc'
GROUP BY C1.ID, C1.Name
)a

KH
[spoiler]Time is always against us[/spoiler]
khtan
If we just add rollup instead of using the derived table wont work?
I mean
SELECT C1.ID, C1.Name, Count(*)as Counts
FROM ClassifiedsView_Ads AS CA
INNER JOIN Classifieds_Categories AS C1 ON CA.CategoryId = C1.ID
INNER JOIN Classifieds_Members AS M ON CA.MemberID = M.ID
WHERE
(AdStatus = 100) AND M.AspNetUsername= 'abc'
GROUP BY C1.ID, C1.Name with Rollup

You would get both summarised and detailed data which is not recommended

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources