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
Share:
18,965
Ram
Author by

Ram

trying to become good learner.

Updated on June 04, 2022

Comments

  • Ram
    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 the Count 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