Why is the count of join table items so high?-CodePudding

I have a Transact SQL query which returns huge values in the Count field.

SELECT max(l.Date)
      ,count(stp.Key) as 'Count'
      ,wm.Id
  FROM Web wm
  LEFT JOIN Profile stp ON wm.WebId = stp.WebId
  LEFT JOIN Log l ON wm.WebId = l.WebId
  WHERE Code = 'RR'
  GROUP By wm.Id
  ORDER BY count(stp.Key) DESC

If I replace the count(stp.Key) element with (select count(Key) from Profile where wm.Id = Id) then I get the real count:

SELECT max(l.Date)
      ,(select count(Key) from Profile where wm.Id = Id) as 'Count'
      ,wm.Id
  FROM Web wm
  LEFT JOIN Profile stp ON wm.WebId = stp.WebId
  LEFT JOIN Log l ON wm.WebId = l.WebId
  WHERE Code = 'RR'
  GROUP By wm.Id
  ORDER BY (select count(Key) from Profile where wm.Id = Id) DESC

For example, with the simple count in place the top result shows 147,000 but with the select ... in place it shows just 65, etc.

I believe this is because the count is counting every record in the final result set. But how do I get it to only count the stp.Key for the records which match the Id field?

CodePudding user response：

try with distinct, if stp.Key is unique

count(distinct stp.Key)

CodePudding user response：

Try to avoid Log table in join that may give you the correct Result. Use it as subquery By following way

    SELECT 
(select top 1 Date from Log where WebId=wm.WebId order by Date desc)
  ,count(stp.Key) as 'Count'
  ,wm.Id
FROM Web wm LEFT JOIN Profile stp ON wm.WebId = stp.WebId
WHERE Code = 'RR'