Rode Rode - 11 days ago 5
SQL Question

SQL Avoid multiplication on inner joins with several returns

OK, not the best title but could not explain it better.

I have a SQL query with a line like this.

count(PRStatusChangesLog.EffectiveMinutes) as timeInHandoverExternal


it works so far but I also want to add something like this
COUNT (distinct a.ActionId) as 'Number Of Actions'
,

which requires this

INNER JOIN PRAction a on a.PrId = PRHeader.prid


Now the problem which I am sure some of you have already seen. The previous count is now multiplied by the number of actions.

I can see why this happens but I am not sure how best to do this so I can get both the number of actions and the right count without the multiplier.

Simplified full query

SELECT
PRHeader.PrId,
COUNT (distinct a.ActionId) AS 'Number Of Actions',
COUNT (PRStatusChangesLog.EffectiveMinutes) AS timeInHandoverExternal
FROM
PRHeader
LEFT JOIN
PRStatusChangesLog ON PRStatusChangesLog.PrId = PRHeader.PrId
AND PRStatusChangesLog.StatusId = 4100
INNER JOIN
PRAction a ON a.PrId = PRHeader.prid
WHERE
DATEDIFF(mm, prheader.ClosedDate, getdate()) = 1
AND (PRHeader.siteId = 74)
AND prheader.PRTypeId IN (17, 19)
AND PRHeader.tmpStatusId <> 6010
GROUP BY
PRHeader.PrId

Answer
  1. You can count a unique column with DISTINCT like COUNT(DISTINCT PRStatusChangesLog.id).
  2. If this is not possible use a subquery for counting the actions. In the SELECT clause you should write something like: (SELECT COUNT(DISTINCT a.ActionId) FROM ... WHERE PRAction a on a.PrId = PRHeader.prid) AS action_count