title: Division using MySQL with Group By
body: <p>I am looking at the previous post on "division using count" and seems to be having problem in the divide by part. Below is my query, I get the results but the results is wrong because the "divide by" with <code>count(*)</code> seems to be using the count of all the stores and not group by the stores.</p>
  
  <p>What I am interested is the "divide by "should also be based on the number of counts for that particular store. my second part of the query for the divide by should be something like "<code>select count(*) *100 group by stores</code>"</p>
  
  <pre><code>SELECT store, CAST(((  
      SUM(CASE WHEN SCORE BETWEEN 9 AND 10 THEN 1 ELSE 0 END) * 1.0 -
      SUM(CASE WHEN SCORE BETWEEN 0 AND 6 THEN 1 ELSE 0 END)) / COUNT(*) * 100) AS signed) AS NPS
  FROM npsnetsuite
  WHERE SCORE IS NOT NULL group by STORE;
  </code></pre>
  
  <p>example:</p>
  
  <pre><code>Store A has got total count of 7 rows
  Store B has got total count of 12 rows
  </code></pre>
  
  <p>the division should be </p>
  
  <pre><code>XX/7 for Store A
  XX/12 for Store B
  </code></pre>
  
  <p><strong>Sample Data:</strong> </p>
  
  <pre><code>Date       Store    Score   Case When between 9-10  case when 0-6
  18/04/2016  A         9                  1  
  20/04/2016  A         8     
  26/04/2016  A         10                 1  
  29/04/2016  A         8     
  30/04/2016  A        10                  1  
  01/05/2016  A        10                  1  
  06/05/2016  A        8      
  11/05/2016  A        10                  1  
  23/05/2016  A        10                  1  
  04/06/2016  A         6                               1
  09/06/2016  A         8     
  08/06/2016  A        10                  1  
  05/04/2016  B         4                               1 
  07/04/2016  B        10                  1  
  18/04/2016  B        10                  1  
  27/04/2016  B         8     
  01/05/2016  B         9                  1  
  13/05/2016  B        10                  1  
  13/06/2016  B         1                              1
  14/06/2016  B         6                              1
  </code></pre>
  
  <p>Sample Data:
  <a href="https://i.stack.imgur.com/I1xXj.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/I1xXj.jpg" alt="Sample Data"></a></p>
  
  <p><strong>Desired Output:</strong></p>
  
  <pre><code>Store A 50
  Store B 13
  </code></pre>
  
  <p>So basically <code>Store A is (7-1)/12 * 100 = 50</code>.</p>
  
  <p><code>7</code> is the count when case when for score <code>9 to 10</code> and <code>1</code> is the count when case when for score <code>0 to 6</code>. So the first part which i have got it correct is  count of number of score 9to10 MINUS count of number of score 0to6 for each store. And i need to divide by the number of count for that particular stores. As above example Store A contain 12 rows and Store B contain 8 rows. My current query is taking the count of all stores.</p>
  
  <p>Thank you</p>