Count occurence of members in Set type

Question

I have a table like this:

  +----+-----------+---------+
  | ID | monday    | tuesday | 
  +---+-----------+----------+
  |  1 | 1,2,3,4,5 | 3,4,5,6 |
  |  2 | 3,4,5     | 5,6,7   |
  +----+-----------+---------+

Where monday and tuesday are SET type columns. Each number represents an hour of availability for the given day of the week. I want to count the number of entries that have each hour available so I can see when the most respondents are free.

For example, on monday, 3 would have a count of 2 and for tuesday,3 would have a count of 1. I'd like to use as few queries as possible.

I've been working on this for hours and can't figure it out. Any help would be appreciated! Suggestions on how to do this better would be appreciated too! Thanks!!

You said you "want to count the number of entries that have each hour available". That means you need the name of a day containing something like an array of consecutive entries from 1 to 8 (or whatever hour)? Also, on tuesday I guess 3 has a count of 1, not 0 (the first entry is available at 3). — Ariel Chelsău, Dec 15 '12 at 09:51
this seems like a poor way to handle a weekly schedule. I'd setup a table with columns like.. `id | day_of_week | hours_available` instead of each day being it's own column. Same results, but it might be easier to query this way. — Sterling Archer, Dec 15 '12 at 09:59

score 0 · Accepted Answer · edited May 23 '17 at 11:48

As @PRPGFerret suggests, you really ought to normalise your schema (see @BillKarwin's answer to "Is storing a comma separated list in a database column really that bad?"):

CREATE TABLE Hours (Hour TINYINT UNSIGNED NOT NULL);
INSERT INTO Hours (Hour) VALUES
  ( 0),( 1),( 2),( 3),( 4),( 5),( 6),( 7),( 8),( 9),(10),(11),
  (12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)
;

CREATE TABLE HoursAvailable (
  Day  ENUM(
         'Monday',
         'Tuesday',
         'Wednesday',
         'Thursday',
         'Friday',
         'Saturday',
         'Sunday'
       ) NOT NULL,
  ID   BIGINT  UNSIGNED NOT NULL,
  Hour TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (Hour, Day, ID)
);

INSERT INTO HoursAvailable (Day, ID, Hour)
  SELECT 'Monday', ID, Hour
  FROM   my_table JOIN Hours ON FIND_IN_SET(Hours.Hour, my_table.monday)
UNION ALL
  SELECT 'Tuesday', ID, Hour
  FROM   my_table JOIN Hours ON FIND_IN_SET(Hours.Hour, my_table.tuesday)
;

DROP TABLE Hours;
DROP TABLE my_table;

Then you can do:

SELECT Day, COUNT(*) FROM HoursAvailable WHERE Hour = 3 GROUP BY Day;

See it on sqlfiddle.

Without normalising your data in this fashion, you could do something rather inefficient:

SELECT SUM(FIND_IN_SET(3, monday )>0),
       SUM(FIND_IN_SET(3, tuesday)>0)
FROM   my_table;

See it on sqlfiddle.

Note that, as observed by @ArielChelsău, Tuesday has a count of 1 rather than the 0 mentioned in the question.

Thank you so much! This answer goes far beyond what I was expecting! I was thinking it'd need its own table, but wasn't sure if there was an easier way to work with the SET types and I was just missing it. I'll normalize the values as you suggest. — NicholasJohn16, Dec 16 '12 at 01:41

Count occurence of members in Set type

1 Answers1