title: Return 0 when table does not contain a row that references another table
body: <p>I have two tables, <strong>Kurv</strong> and <strong>Kurvtilmeldingsvalg</strong>. Each row in <strong>Kurvtilmeldingsvalg</strong> contains a foreign key to a row in <strong>Kurv</strong>. However, when the <strong>Kurv</strong> is first created, no rows in <strong>Kurvtilmeldingsvalg</strong> exist yet.</p>
  <p>I have a script that calculates a number based on some values in <strong>Kurvtilmeldingsvalg</strong>. However, if a row in this table does not yet exist, then the calculation should return 0.</p>
  <p>Currently I am using CASE WHEN conditions to calculate the correct sum. However, this does not take non-existing rows in the <strong>Kurvtilmeldingsvalg</strong> table into account.</p>
  <pre><code>SELECT Kurv.Id AS KurvID,
         ISNULL(SUM(CASE
                        WHEN KurvTilmeldingsvalg.Antal - KurvTilmeldingsvalg.OriginalAntalSolgte &lt; 0 THEN 0
                        ELSE (KurvTilmeldingsvalg.Antal - KurvTilmeldingsvalg.OriginalAntalSolgte)
                    END * nc_valglinjeBase.nc_deltageroptaelling), 1) AS TotalAntal
  FROM Kurv
  LEFT JOIN KurvTilmeldingsvalg ON Kurv.Id = KurvTilmeldingsvalg.KurvId
  LEFT JOIN nc_valglinjeBase ON KurvTilmeldingsvalg.Valglinje = nc_valglinjeBase.nc_valglinjeId
  WHERE Kurv.Status in (0,
                        1,
                        2,
                        13/*,14*/)
  GROUP BY Kurv.Id
  ORDER BY TotalAntal DESC
  </code></pre>
  <p>I attempted a solution by doing a subquery inside the WHEN clause:</p>
  <pre><code>WHEN (Select Count(*) FROM Kurvtilmeldingsvalg INNER JOIN Kurv ON Kurvtilmeldingsvalg.KurvId = Kurv.Id) = 0 THEN 0
  </code></pre>
  <p>However i get an error with the following message: &quot;cannot perform an aggregate function on an expression containing an aggregate or a subquery&quot;.</p>
  <p>Current incorrect output:</p>
  <p><a href="https://i.stack.imgur.com/x8UuC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/x8UuC.png" alt="enter image description here" /></a></p>
  <p>Desired output would return 0 instead of 1. The <strong>Kurv</strong> in this example is not referenced by any rows in <strong>Kurvtilmeldingsvalg</strong>.</p>
  <p>How can i return 0 when there are no rows in the <strong>Kurvtilmeldingsvalg</strong> table that reference a row in <strong>Kurv</strong>?</p>