title: SQL Distinct with multiple Criteria
body: <p>I want to produce a table output as per the <a href="https://i.stack.imgur.com/kcus2.jpg" rel="nofollow noreferrer">SampleData Table &amp; Output</a>. My SQL Query is as follows but it is not giving me the right result for last three columns</p>
  
  <pre><code>SELECT
    UserLogin,
    COUNT(DISTINCT consumerID) AS TotalUniqueconsumerIDs,  
    COUNT(DISTINCT (CASE
          WHEN ActivitiesCompleted &gt; 0 AND QuizCompleted &lt;= 0 THEN consumerID END)) AS YesActivityNoQuiz,
    COUNT(DISTINCT (CASE
          WHEN ActivitiesCompleted &lt;= 0 AND QuizCompleted &gt; 0 THEN consumerID END)) AS NoActivityYesQuiz,  
  COUNT(DISTINCT (CASE
          WHEN ActivitiesCompleted &gt;= 1 AND QuizCompleted &gt;= 1 THEN consumerID END)) AS YesActivityYesQuiz
  FROM
    TableName
  GROUP BY
    UserLogin
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/kcus2.jpg" alt="SampleData Table &amp; Output"></p>
  
  <p>Any help is highly appreciated.</p>
  
  <hr>
  
  <p>More info:</p>
  
  <p>Perhaps I wasn't clear above so providing more info here:</p>
  
  <p>I created another sample table with just data of 1 UserLogin which looks like <a href="https://i.stack.imgur.com/VAN6B.jpg" rel="nofollow noreferrer">this</a> and if I pivot this sample data set externally in Excel I see <a href="https://i.stack.imgur.com/rY5fQ.jpg" rel="nofollow noreferrer">this</a></p>
  
  <p>As you can see that this pivot screenshot both of the consumerID in this table has more than 1 ActivitiesCompleted as well as 1 QuizCompleted.</p>
  
  <p>The SQL Output based on my query above looks like <a href="https://i.stack.imgur.com/uhJLq.jpg" rel="nofollow noreferrer">this</a></p>
  
  <p>However, based on Pivot screenshot, I am expecting 1 in YesActivityYesQuiz column and zero in YesActivityNoQuiz &amp; NoActivityYesQuiz</p>
  
  <hr>
  
  <p>Sample Data:</p>
  
  <pre><code>Date,UserLogin,consumerID,ActivitiesCompleted,QuizCompleted
  6/8/2018,aasufhar,consumerA,0,1
  6/8/2018,aasufhar,consumerA,0,0
  6/8/2018,aasufhar,consumerA,1,0
  6/8/2018,aasufhar,consumerA,1,0
  6/8/2018,aasufhar,consumerA,1,0
  6/8/2018,aasufhar,consumerA,1,0
  6/8/2018,aasufhar,consumerA,1,0
  6/8/2018,aasufhar,consumerA,1,0
  6/8/2018,aasufhar,consumerA,1,0
  6/8/2018,aasufhar,consumerA,2,0
  6/8/2018,aasufhar,consumerA,1,0
  6/8/2018,aasufhar,consumerA,1,0
  6/8/2018,aasufhar,consumerA,1,0
  6/8/2018,aasufhar,consumerA,0,0
  6/8/2018,aasufhar,consumerA,0,0
  6/8/2018,aasufhar,consumerA,0,0
  6/8/2018,aasufhar,consumerA,0,0
  6/8/2018,aasufhar,consumerA,0,0
  6/8/2018,aasufhar,consumerA,0,0
  6/8/2018,aasufhar,consumerA,0,0
  6/8/2018,aasufhar,consumerA,0,0
  6/8/2018,aasufhar,consumerA,0,0
  6/8/2018,aasufhar,consumerA,0,0
  6/8/2018,aasufhar,consumerA,0,0
  6/8/2018,aasufhar,consumerB,0,1
  6/8/2018,aasufhar,consumerB,0,0
  6/8/2018,aasufhar,consumerB,2,0
  6/8/2018,aasufhar,consumerB,1,0
  6/8/2018,aasufhar,consumerB,1,0
  6/8/2018,aasufhar,consumerB,1,0
  6/8/2018,aasufhar,consumerB,0,0
  6/8/2018,aasufhar,consumerB,0,0
  6/8/2018,aasufhar,consumerB,0,0
  6/8/2018,aasufhar,consumerB,0,0
  6/8/2018,aasufhar,consumerB,0,0
  </code></pre>