CODEWITHSUNDEEP
sql-servercountrowinner-join
Ram
Ram

Reputation: 1141

how to get count in sql server while using inner joins?

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

Upvotes: 2

Views: 19760

Answers (2)

Taryn
Taryn

Reputation: 247680

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

Upvotes: 5

Raj
Raj

Reputation: 10843

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) 
GROUP BY h.helpcontent, s.MenuID, s.pkSitePageId, s.PageAliasName,
s.SitePageName,s.pagepath
ORDER BY s.pkSitePageId

Upvotes: 0

Related Questions