CODEWITHSUNDEEP
mysqlsql
tristanbailey
tristanbailey

Reputation: 4605

SQL Select count of categories accross multiple columns

I have a data structure in the table of these columns 

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

Without the ability to change the table structure I need to "count the number for Offers per Category across the 3 columns"

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.

I need to get a list of all the Categories and then put offers against them.

Offer can be in the table more than once.

As far as I have got is do a temp table first with a UNION.

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;

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.

So trying to work out how to figure this should work and the SQL could be totally different with the answer:

   Joint Category  | Total Count of offers
   Browns          | 3   
   White           | 3  
   Greens          | 2  
   Yellow          | 2

Upvotes: 0

Views: 2125

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269653

Your results are a bit confusing . . . I cannot tell why browns and whites both have a count of 3. I think you are counting the combination of level and category.

I would be inclined to approach this using union all and then use count() or count(distinct), depending on what the counting logic really is. For the combination of level and category:

SELECT cat, COUNT(DISTINCT level, title) as numtitles
FROM ((SELECT title, 1 as level, category_level1 as cat FROM a_table) union all
      (SELECT title, 2 as level, category_level2 as cat FROM a_table) union all
      (SELECT title, 3 as level, category_level3 as cat FROM a_table)
     ) tc
WHERE cat is not null
GROUP BY cat;

You can include the date column in each of the subqueries and then include a condition in the WHERE clause.

Upvotes: 1

amdixon
amdixon

Reputation: 3833

plan

  • take a union of all distinct categories, alias to Joint Category
  • aggregate count over Joint Category ( where not null or blank - not clear from your rendering if those fields are null or blank.. ) grouping by Joint Category

query

select `Joint Category`, count(*) as `Total Count of offers`
from
(
  select Title, Category_level_1 as `Joint Category`
  from a_table
  union 
  select Title, Category_level_2
  from a_table
  union 
  select Title, Category_level_3
  from a_table
) allcats
where `Joint Category` is not null
and `Joint Category` <> ''
group by `Joint Category`
;

output

+----------------+-----------------------+
| Joint Category | Total Count of offers |
+----------------+-----------------------+
| Browns         |                     3 |
| Greens         |                     3 |
| White          |                     2 |
| Yellow         |                     2 |
+----------------+-----------------------+

sqlfiddle

Upvotes: 1

Related Questions