SQL. Wrong result of COUNT() function

Question

I can't find problem. I have many tables joined each other. I need to get count of form instances from FormInstances table (should count Id column), but It returing wrong data.

So my query looks like:

DECLARE @dateStart  DATE        = NULL,
@dateEnd        DATE            = NULL,
@workshop       NVARCHAR(60)    = '',
@shiftNr        NVARCHAR(60)    = '',
@article        NVARCHAR(60)    = '',
@controllier    NVARCHAR(60)    = 'Name of Controlier'

SELECT COUNT(FI.Id) AS [Count of Instances]
FROM FormInstances AS FI
LEFT JOIN FormFields        AS FF
    ON FI.FormVersionId = FF.FormVersionid 
LEFT JOIN .....
WHERE
FF.Id               =   FV.FormFieldId      AND
FI.Id               =   ...

OUTPUT FOR NOW:

Count of Instances
     414400
     414400
     414400
     414400
     384800

OUTPUT SHOULD BE:

Count of Instances
       5

If I pass to SELECT clause: FI.Id (Id column from FormInstances table) in following:

SELECT COUNT(FI.Id) AS [Count of Instances],
             FI.Id  AS [Instance Ids]
FROM....

I got correct Instance Ids but the same, wrong count of instances:

Count of Instances    Instance Ids
      414400              23
      414400              24
      414400              25
      414400              26
      384800              52

So why It returning incorrect count of instances?

Answer 1

You are doing a join, which is multiplying the number of records. Try using distinct within the count to remove duplicates:

COUNT(distinct FI.Id)

Answer 2

If you just want to get:

 Count of Instances
        5

You should change:

SELECT COUNT(FI.Id) AS [Count of Instances],
             FI.Id  AS [Instance Ids]
FROM....

To this:

SELECT COUNT(*) AS [Count of Instances]
FROM....

But I don't see how the query you posted can return the results you say you are getting so I think you've probably made a mistake in the question.