title: SQL Select count of categories accross multiple columns
body: <p>I have a data structure in the table of these columns  </p>
  
  <pre><code>ID | Title | Category_level_1 | Category_level_2 | Category_level_3
  1 | offer 1 | Browns          | Greens           | White   
  2 | offer 1 | Browns          | White            |   
  3 | offer 2 | Greens          | Yellow           |     
  4 | offer 3 | Browns          | Greens           |      
  5 | offer 4 | Browns          | Yellow           | White    
  </code></pre>
  
  <p>Without the ability to change the table structure I need to "count the number for Offers per Category across the 3 columns"</p>
  
  <p>There is also columns for date range of the offer, to limit to the current ones, but I want to work out the query first.</p>
  
  <p>I need to get a list of all the Categories and then put offers against them.</p>
  
  <p>Offer can be in the table more than once.</p>
  
  <p>As far as I have got is do a temp table first with a UNION.</p>
  
  <pre><code>CREATE TEMPORARY TABLE IF NOT EXISTS Cats AS 
  ( SELECT DISTINCT(opt) FROM (
      SELECT Category_level_1 AS opt FROM a_table
      UNION
      SELECT Category_level_2 AS opt FROM a_table
      UNION
      SELECT Category_level_3 AS opt FROM a_table
    ) AS Temp 
  ) ;
  
  
    SELECT 
      Cats.opt AS "Joint Cat",
      (
          SELECT count(*)
          FROM a_table 
          WHERE a_table.`Category_level_1` = Cats.opt
          OR a_table.`Category_level_2` = Cats.opt
          OR a_table.`Category_level_3` = Cats.opt
          GROUP BY a_table.Title
      ) As Total
      FROM Cats 
          WHERE Category_level_1 != '' 
          ORDER BY Category_level_1 ASC;
  </code></pre>
  
  <p>ISSUE:
  a) so the union works well and I get my values. DONE
  b) the Total subselect though is not grouping correctly.
  I just want a count of all the rows returned but it is grouping with a count of the row titles not all rows.</p>
  
  <p>So trying to work out how to figure this should work and the SQL could be totally different with the answer:</p>
  
  <pre><code>   Joint Category  | Total Count of offers
     Browns          | 3   
     White           | 3  
     Greens          | 2  
     Yellow          | 2 
  </code></pre>