title: SQL: Selecting same Table Column twice based on different WHERE conditions
body: <p>I have already tried various proposed solutions here but none of them have proposed what I need.</p>
  
  <p>I need one result of 2 Queries with each one WHERE condition of 1 Table. No Union result please. Both query results need to be in different Columns packed in one result. Let me show you what I mean:</p>
  
  <p>Query 1:</p>
  
  <p><a href="https://i.stack.imgur.com/OenAS.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/OenAS.jpg" alt="This is the result from the first query"></a></p>
  
  <pre><code>SELECT 
      tbl_Abteilungen.Bezeichnung AS Abteilung, 
      COUNT(tbl_Abteilungen.abteilungen_id) AS [Offene Abteilungstermine] 
  FROM 
      tbl_Lieferschein
  INNER JOIN 
      tbl_Positionen ON tbl_Lieferschein.lieferschein_id = tbl_Positionen.id_lieferschein
  INNER JOIN 
      tbl_Positionen_Abteilungen ON tbl_Positionen.positionen_id = tbl_Positionen_Abteilungen.id_positionen
  INNER JOIN 
      tbl_Abteilungen ON tbl_Positionen_Abteilungen.id_abteilungen = tbl_Abteilungen.abteilungen_id
  WHERE 
      tbl_Positionen_Abteilungen.fertiggestellt = 0 
      AND tbl_Lieferschein.fertiggestellt = 0 
      AND tbl_Lieferschein.gelöscht = 0
  GROUP BY 
      tbl_Abteilungen.Bezeichnung
  </code></pre>
  
  <p>Query 2:</p>
  
  <p><a href="https://i.stack.imgur.com/6FIiM.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6FIiM.jpg" alt="This is the result from the second query"></a></p>
  
  <pre><code>SELECT 
      COUNT(tbl_Abteilungen.abteilungen_id) AS [Überfällige Abteilungstermine] 
  FROM
      tbl_Lieferschein
  INNER JOIN 
      tbl_Positionen ON tbl_Lieferschein.lieferschein_id = tbl_Positionen.id_lieferschein
  INNER JOIN 
      tbl_Positionen_Abteilungen ON tbl_Positionen.positionen_id = tbl_Positionen_Abteilungen.id_positionen
  INNER JOIN 
      tbl_Abteilungen ON tbl_Positionen_Abteilungen.id_abteilungen = tbl_Abteilungen.abteilungen_id
  WHERE 
      tbl_Positionen_Abteilungen.fertiggestellt = 0 
      AND tbl_Lieferschein.fertiggestellt = 0 
      AND tbl_Lieferschein.gelöscht = 0 
      AND tbl_Positionen_Abteilungen.Abteilungstermin &lt; CAST(GETDATE() AS DATE)
  GROUP BY 
      tbl_Abteilungen.Bezeichnung
  </code></pre>
  
  <p>All I need is a result like the following:</p>
  
  <p>Needed result:</p>
  
  <p><a href="https://i.stack.imgur.com/SaPBS.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SaPBS.jpg" alt="Needed result"></a></p>
  
  <p>PS: The best would be, if all null results would be shown as '0'</p>