title: Loop/JOIN results dynamically using UNION
body: <p>I have a table that looks like following</p>
  
  <pre><code>| id | views                                                                     | actions                                                                   |
  |----|---------------------------------------------------------------------------|---------------------------------------------------------------------------|
  | 1  | {"group1":{"mobile":20,"desktop":36},"group2":{"mobile":25,"desktop":41}} | {"group1":{"mobile":4,"desktop":3},"group2":{"mobile":2,"desktop":4}}     |
  | 2  | {"group1":{"mobile":40,"desktop":34},"group2":{"mobile":31,"desktop":45}} | {"group1":{"mobile":21,"desktop":19},"group2":{"mobile":23,"desktop":19}} |
  | 3  | {"group1":{"mobile":53,"desktop":34},"group2":{"mobile":37,"desktop":75}} | {"group1":{"mobile":17,"desktop":14},"group2":{"mobile":30,"desktop":12}} |
  </code></pre>
  
  <p>And I want my results to look like following</p>
  
  <pre><code>| product     | views group 1  | views group 2  | actions group 1 | actions group 2 | actions per view group 1 | actions per view group 2 |
  |-------------|----------------|----------------|-----------------|-----------------|--------------------------|--------------------------|
  | 1 (mobile)  | 20             | 25             | 4               | 2               | (D2/B2) = 0.2            | (E2/C2) = 0.08           |
  | 1 (desktop) | 36             | 41             | 3               | 4               | (D3/B3) = 0.115          | (E2/C2) = 0.09           |
  | 2 (mobile)  | 40             | 31             | 21              | 23              | ...                      | ...                      |
  | 2 (desktop) | 34             | 45             | 19              | 19              | ...                      | ...                      |
  | 1 (overall) | (B2 + B3) = 56 | (C2 + C3) = 66 | ...             | ...             | ...                      | ...                      |
  | 2 (overall) | (B4 + B5) = 84 | (C4 + C5) = 76 | ...             | ...             | ...                      | ...                      |
  </code></pre>
  
  <p>I can do it using the following SQL query, but I'm wondering if I can do it in a more cleaner or shorter way</p>
  
  <pre><code>SELECT
      CASE id WHEN 1 THEN '1 (mobile)' WHEN 2 THEN '2 (mobile)' END as product,
      SUM(json_extract_path_text(actions, 'group1', 'mobile') AS actions_group_1,
      SUM(json_extract_path_text(views, 'group1', 'mobile') AS views_group_1,
      SUM(json_extract_path_text(actions, 'group2', 'mobile') AS actions_group_2,
      SUM(json_extract_path_text(views, 'group2', 'mobile') AS views_group_2,
      SUM(json_extract_path_text(actions, 'group1', 'mobile')/SUM(json_extract_path_text(views, 'group1', 'mobile') AS actions_per_view_group_1 ,
      SUM(json_extract_path_text(actions, 'group2', 'mobile')/SUM(json_extract_path_text(views, 'group2', 'mobile') AS actions_per_view_group_2
  FROM widget_daily_stats
  WHERE id IN (1,2)
  GROUP BY id
  
  UNION
  
  SELECT
      CASE id WHEN 1 THEN '1 (desktop)' WHEN 2 THEN '2 (desktop)' END as product,
      SUM(json_extract_path_text(actions, 'group1', 'desktop') AS actions_group_1,
      SUM(json_extract_path_text(views, 'group1', 'desktop') AS views_group_1,
      SUM(json_extract_path_text(actions, 'group2', 'desktop') AS actions_group_2,
      SUM(json_extract_path_text(views, 'group2', 'desktop') AS views_group_2,
      SUM(json_extract_path_text(actions, 'group1', 'desktop')/SUM(json_extract_path_text(views, 'group1', 'desktop') AS actions_per_view_group_1 ,
      SUM(json_extract_path_text(actions, 'group2', 'desktop')/SUM(json_extract_path_text(views, 'group2', 'desktop') AS actions_per_view_group_2
  FROM widget_daily_stats
  WHERE id IN (1,2)
  GROUP BY id
  
  UNION
  
  ...
  </code></pre>
  
  <p>There has to be a way for me to loop through mobile and desktop and dynamically UNION the results without having to explicitly UNION all the results</p>