2

I'm trying to create a metabase query, where I want to calculate the % of a particular feedback-label by total, grouped by start-batter

  1. I count all columns by start-battery
  2. I then join that count with the original data again, so each column has the total of that start battery.
  3. I then count again by start-battery and feedback-label

enter image description here

SQL

SELECT 
    "source"."START_BATTERY" AS "START_BATTERY", 
    "source"."count" AS "count", 
    "Ride Dev"."FEEDBACK_TAGS_STRING" AS "FEEDBACK_TAGS_STRING", 
    COUNT(*) AS "count"
FROM 
    (SELECT 
         "MART"."CORE"."RIDE_DEV"."START_BATTERY" AS "START_BATTERY", 
         COUNT(*) AS "count" 
     FROM 
         "MART"."CORE"."RIDE_DEV"
     WHERE 
         ((NOT (LOWER("MART"."CORE"."RIDE_DEV"."FEEDBACK_TAGS_STRING") LIKE '%,%')
           OR "MART"."CORE"."RIDE_DEV"."FEEDBACK_TAGS_STRING" IS NULL)
          AND "MART"."CORE"."RIDE_DEV"."START_BATTERY" <= 100 
          AND "MART"."CORE"."RIDE_DEV"."START_BATTERY" > 20 
          AND "MART"."CORE"."RIDE_DEV"."FEEDBACK_TAGS" IS NOT NULL 
          AND "MART"."CORE"."RIDE_DEV"."VEHICLE_MODEL" = 'Voiager 3X (NB Max)')
    GROUP BY 
        "MART"."CORE"."RIDE_DEV"."START_BATTERY"
    ORDER BY 
        "MART"."CORE"."RIDE_DEV"."START_BATTERY" ASC) "source"
LEFT JOIN 
    "MART"."CORE"."RIDE_DEV" "Ride Dev" ON "source"."START_BATTERY" = "Ride Dev"."START_BATTERY" 
WHERE 
    ("Ride Dev"."FEEDBACK_TAGS_STRING" IS NOT NULL 
     AND ("Ride Dev"."FEEDBACK_TAGS_STRING" <> '' OR "Ride Dev"."FEEDBACK_TAGS_STRING" IS NULL) 
     AND (NOT (LOWER("Ride Dev"."FEEDBACK_TAGS_STRING") LIKE '%,%') 
               OR "Ride Dev"."FEEDBACK_TAGS_STRING" IS NULL)) 
GROUP BY 
    "source"."START_BATTERY", "source"."count", 
    "Ride Dev"."FEEDBACK_TAGS_STRING" ORDER BY "source"."START_BATTERY" ASC, 
    "source"."count" ASC, "Ride Dev"."FEEDBACK_TAGS_STRING" ASC

Result

Start Battery   Count   Ride Dev → Feedback Tags String Count
21              6598    couldnt_end_ride                916
21              6598    motor_didnt_start               2
21              6598    other                           1
21              6598    scooter_didnt_start             1569
21              6598    slow                            3667
21              6598    stopped_during_ride             3150
22              6815    couldnt_end_ride                866
22              6815    other                           2
22              6815    scooter_didnt_start             1617
22              6815    screws_missing                  1
  1. Is it possible to name the counted columns so they are easier to keep apart?
  2. I'd like to create a calculated field with count / Ride Dev → Count I click summarize and add custom field [Ride Dev.coun] / [Count] but it doesn't recognize the first field. How can I reference the first field?
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Himmators
  • 14,278
  • 36
  • 132
  • 223

0 Answers0