November 30, 2007 at 11:26 pm
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
December 1, 2007 at 3:04 am
May be this way:
SELECT Sum(query.Counts) AS Total
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 ) AS query
December 1, 2007 at 4:44 am
Or may be this would also do....
SELECTCOUNT( * ) AS Counts
FROMClassifiedsView_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
WHEREAdStatus = 100 AND M.AspNetUsername = 'abc'
--Ramesh
December 1, 2007 at 9:25 am
thnks a lot.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply