title: SQL Query data with count grouped
body: <p>I have the following data in one database table (Columns A to E). I need a query to show the same columns (A to E), adding two extra columns.</p>
  
  <p>In Excel I used the following formula's to do this, but I'm not able to do it with a query (SQL Server):</p>
  
  <pre><code>=SI(CONTAR.SI(C$2:C2;C2)=1;1;0)
  =(CONTAR.SI.CONJUNTO(C$2:C2;C2;$D$2:D2;D2)=1)*1
  
  A ----- B ----- C ----- D ----- E ----- COUNT (DISTINCT C) ----- COUNT (DISTINCT C&amp;D)
  1 ----- 26 -- 4650 ---- 1 ---- 11 ----- 1 ---------------------------------- 1
  1 ----- 26 -- 4650 ---- 1 ---- 22 ----- 0 ---------------------------------- 0
  1 ----- 26 -- 4650 ---- 9 ---- 11 ----- 0 ---------------------------------- 1
  1 ----- 26 -- 4650 ---- 9 ---- 22 ----- 0 ---------------------------------- 0
  1 ----- 26 -- 50934 --- 1 ---- 11 ----- 1 ---------------------------------- 1
  1 ----- 26 -- 50934 --- 1 ---- 22 ----- 0 ---------------------------------- 0
  1 ----- 26 -- 50934 --- 1 ---- 40 ----- 0 ---------------------------------- 0
  1 ----- 26 -- 51097 --- 3 ---- 40 ----- 1 ---------------------------------- 1
  1 ----- 26 -- 51097 --- 3 ---- 41 ----- 0 ---------------------------------- 0
  1 ----- 26 -- 51097 --- 4 ---- 40 ----- 0 ---------------------------------- 1
  1 ----- 26 -- 51097 --- 4 ---- 41 ----- 0 ---------------------------------- 0
  3 ----- 34 -- 50099 --- 1 ---- 40 ----- 1 ---------------------------------- 1
  3 ----- 34 -- 50099 --- 1 ---- 41 ----- 0 ---------------------------------- 0
  3 ----- 34 -- 50099 --- 1 ---- 42 ----- 0 ---------------------------------- 0
  </code></pre>