title: Using Case and Group By to get Grouped results in a specific order
body: <p>I have asked a similar question in the past, but didn't quite get what I needed, here's the old question</p>
  
  <p><a href="https://stackoverflow.com/questions/7663744/how-do-i-get-the-id-of-a-rows-which-have-max-and-min-values-in-sql">How do I get the ID of a rows which have MAX and MIN values in SQL</a></p>
  
  <p>What I need to do is get a set of data which has grouped all items with the same <code>item</code> field in the database, but ensure that the data set outputs the details relating to a specific item ID, so to order the grouping in a specific fashion. (in my case I need to order by either <code>totalStock</code> field, or <code>price</code>, if <code>totalStock</code> then order <code>desc</code> if <code>price</code>, order <code>asc</code>.</p>
  
  <p>I <em>think</em> that this SO question answers my question better, but I don't understand the <code>Joins</code> I am afraid</p>
  
  <p><a href="https://stackoverflow.com/questions/5929510/mysql-query-order-the-results-in-group-by">MySQL query order the results in GROUP BY</a></p>
  
  <p>Here's an example of where I'm at:</p>
  
  <pre><code>"SELECT * FROM (select *, count(id) as Variants, sum(totalStock) as sumTotalStock 
  FROM `products` WHERE id &gt; 0 AND `display`='1' 
  GROUP BY item ORDER BY CASE WHEN totalStock &gt; 0 THEN totalStock ELSE price END DESC) 
  AS unknownVar 
  ORDER BY sumTotalStock"
  </code></pre>
  
  <p>My lose understanding of SQL is aiming to get an inner set of results from my database grouped by <code>item</code> with the grouping ordered by <code>totalStock</code> if <code>totalStock &gt; 0</code> or by <code>price</code> otherwise. This inner result set is then order by <code>sumTotalStock</code>.</p>
  
  <p>The important bit is </p>
  
  <pre><code>ORDER BY CASE WHEN totalStock &gt; 0 THEN totalStock ELSE price END
  </code></pre>
  
  <p>This is giving me two issues.</p>
  
  <p>1) some strange output, missing out some content that should be shown.</p>
  
  <p>2) Also, I need to be able to change the sort order</p>
  
  <pre><code>ORDER BY CASE WHEN totalStock &gt; 0 *DESC* THEN totalStock ELSE price *ASC* END
  </code></pre>
  
  <p>With the astrix'd <code>DESC</code> and <code>ASC</code> doesn't work, I seem to have to put <code>DESC</code> at the end of the <code>ORDER BY</code> statement, but then it's ordering <code>DESC</code> in either case, which is wrong.</p>
  
  <p>Given the above two issues, I'm not sure this is actually working at all!</p>
  
  <p>I've spent most of the afternoon and all evening on this now with Google and various SO questions, but I'm afraid I've given up and had to post another!</p>
  
  <p>If anyone could post me some code that would sort this I would be eternally grateful!</p>
  
  <p>I'm using a MySQL database, bother the <code>totalStock</code> and <code>price</code> columns are numeric in all cases and <code>item</code> is a <code>varChar</code> field. Once I can get this working with these fields I can extract all else I need, so hopefully, you shouldn't need any more than that to point me in the right direction?</p>