how to get count in sql server while using inner joins?
18,965
If you are using a aggregate function, then you need to use a GROUP BY
clause on the remaining fields in your SELECT
statement that are not in an aggregate. Similar to this:
SELECT count(p.pkRolePermissionId) CountRolePermission,
h.helpcontent,
s.MenuID,
s.pkSitePageId,
s.PageAliasName,
s.SitePageName,
s.pagepath
from tblSiteRolePermissions p
INNER JOIN tblSitePages s
ON p.fkSitePageId = s.pkSitePageId
inner join tblHelp h
on s.pkSitePageId=h.fkSitePageId
WHERE (p.fkRoleId =4 and p.ViewOnly=1)
GROUP BY h.helpcontent,
s.MenuID,
s.pkSitePageId,
s.PageAliasName,
s.SitePageName,
s.pagepath
ORDER BY s.pkSitePageId
If you want the count of all rows, then you can use something like this:
SELECT NumRows.TotalCount,
h.helpcontent,
s.MenuID,
s.pkSitePageId,
s.PageAliasName,
s.SitePageName,
s.pagepath
from tblSiteRolePermissions p
INNER JOIN tblSitePages s
ON p.fkSitePageId = s.pkSitePageId
inner join tblHelp h
on s.pkSitePageId=h.fkSitePageId
cross join
(
SELECT count(*) TotalCount
from tblSiteRolePermissions p
INNER JOIN tblSitePages s
ON p.fkSitePageId = s.pkSitePageId
inner join tblHelp h
on s.pkSitePageId=h.fkSitePageId
WHERE (p.fkRoleId =4 and p.ViewOnly=1)
) NumRows
WHERE (p.fkRoleId =4 and p.ViewOnly=1)
ORDER BY s.pkSitePageId
Or you can use CTE with your query, similar to this:
;with cte as
(
SELECT h.helpcontent,
s.MenuID,
s.pkSitePageId,
s.PageAliasName,
s.SitePageName,
s.pagepath
from tblSiteRolePermissions p
INNER JOIN tblSitePages s
ON p.fkSitePageId = s.pkSitePageId
inner join tblHelp h
on s.pkSitePageId=h.fkSitePageId
WHERE (p.fkRoleId =4 and p.ViewOnly=1)
)
select h.helpcontent,
s.MenuID,
s.pkSitePageId,
s.PageAliasName,
s.SitePageName,
s.pagepath,
(select count(*) from cte) as Total
from cte
Comments
-
Ram almost 2 years
I have following query:
SELECT h.helpcontent, s.MenuID, s.pkSitePageId, s.PageAliasName, s.SitePageName,s.pagepath FROM tblSiteRolePermissions p INNER JOIN tblSitePages s ON p.fkSitePageId = s.pkSitePageId INNER JOIN tblHelp h ON s.pkSitePageId=h.fkSitePageId WHERE (p.fkRoleId = 4 AND p.ViewOnly = 1) ORDER BY s.pkSitePageId
I want to get count from
tblSiteRolePermissions
. I have tried theCount
but its throwing some error like:Msg 8120, Level 16, State 1, Line 1
Column 'tblHelp.HelpContent' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Please help me and let me know where I am wrong.
Update
Here is the code I am trying:
SELECT count(p.pkRolePermissionId), h.helpcontent, s.MenuID, s.pkSitePageId, s.PageAliasName, s.SitePageName, s.pagepath FROM tblSiteRolePermissions p INNER JOIN tblSitePages s ON p.fkSitePageId = s.pkSitePageId INNER JOIN tblHelp h ON s.pkSitePageId = h.fkSitePageId WHERE (p.fkRoleId = 4 AND p.ViewOnly = 1) ORDER BY s.pkSitePageId