0

All, I'm using the following Sequel query...

select
u.full_name, u.id, sum(hit_count), tag_names
from MyTable as t
inner join MyUsers as u on u.id=t.user_id
where t.created_at >= '2015-1-01 04:00:00' AND t.created_at < '2015-2-01 04:00:00' AND tag_names like '%Tree%'
group by u.id

...to render a table of data that looks like this with about 1,000 rows:

| full_name    | id  | sum(hit_count) | tag_names                 |

| Fred Johnson | 001 | 1500           | Tree, Apple, Blue, Banana |
| Joe Smith    | 002 | 663            | Tree, Red, Pear, Yellow   |    
| Tina Cook    | 001 | 1500           | Tree, Berry, Red, Grape   |
| Kyle Cross   | 001 | 1500           | Tree, Berry, Red, Grape   |

However, because I know the finite number of primary color tags (e.g. Red, Blue, Yellow) available, is there a way to tweak my Sequel query to also group by primary color tag (and add a primary color tag column to my table) to render...

 | full_name    | id  | sum(hit_count) | tag_names                 | Primary_Color_Tag |

 | Fred Johnson | 001 | 1500           | Tree, Apple, Blue, Banana | Blue              | 
 | Joe Smith    | 002 | 500            | Tree, Red, Pear, Yellow   | Red               |  
 | Tina Cook    | 001 | 1500           | Tree, Berry, Red, Grape   | Red               |
 | Kyle Cross   | 001 | 1500           | Tree, Berry, Red, Grape   | Red               | 
 | Joe Smith    | 001 | 163            | Tree, Red, Pear, Yellow   | Yellow            |

I've found similar questions on this forum that have seemingly relevant solutions, but I've been unable to piece together their logic to reproduce the table I'm after. Any insight would be greatly appreciated!

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
Greg
  • 1,264
  • 14
  • 21
  • 1
    Where is this *primary color tag* supposed to come from that you want to group by? You've provided the names of 4 columns (full_name, id, hit_count, and tag_names). Where does *primary_color_tag* magically appear from? (I'm presuming it's from within *tag_names*, but there's no consistency to the position in that list you're retrieving from.) – Ken White Feb 26 '15 at 19:01
  • My apologies for being unclear, Ken. My thinking was that it may be possible to add a new column to the data table using something like this... `SUBSTRING_INDEX(tag_names, ',', 1) as First_Tag` The problem with that particular code, though, is that the order of tag_names entries is unpredictable—e.g. the primary color tag could be second or it could be fourth... – Greg Feb 26 '15 at 20:46
  • However, I was hoping that my knowledge of the precise names I'm attempting to target (Blue, Red, Yellow) might mitigate the issue of inconsistent position somehow. – Greg Feb 26 '15 at 20:52
  • You're going to have extreme difficulty doing this and then doing a GROUP BY on that column. (At the very least, you're going to have to use a sub-query to generate your columns, and then a select from that sub-query to do the grouping, unless your DBMS has some super-power that will do this for you.) I personally would normalize the data (create a separate column for primary color, and populate it independently from primary_color_tag) if you need to use it selectively, and then you can easily add the separate `primary_color` column and group by it in addition to the other columns. – Ken White Feb 26 '15 at 20:59
  • Thank you for your insight, Ken. Unfortunately, I only have viewing access to the two tables I'm querying (MyTable and MyUsers) so, unless I'm misunderstanding you, I'm unable to normalize the data in the way you suggest. – Greg Feb 26 '15 at 21:09
  • That's too bad; it would have been much easier. I'm afraid I can't help with Sequel or Ruby; I could have with plain SQL, but not with those specifically. – Ken White Feb 26 '15 at 21:34

0 Answers0