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
- I count all columns by start-battery
- I then join that count with the original data again, so each column has the total of that start battery.
- I then count again by start-battery and feedback-label
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
- Is it possible to name the counted columns so they are easier to keep apart?
- 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?