First, I’d like to thank the great people who created this software and thank everyone for the amazing support.
I’ll try my best to describe the goal.
- Can you please show me an example or where I may find information on adding multiple queries (Query A, B) because I think that might solve my issue.
I have two tables.
The survey table has the results and date.
The token table has the needed data but only has a date when the survey was completed.
I would like to determine the response rate by Facility. The column usesleft = 0 when someone used that token for a survey.
I want to to count all of the rows for completed surveys for a particular facility (7). Then divide that by the number of tokens / emails sent for that facility (10). this would give me a response rate of 70% from that facility.
This works but it is too slow for 1,000’s of rows so I limited the example to 5.
SELECT Fac as '$__name', ROUND(completed / total * 100,2) AS 'value' FROM ( SELECT (SELECT R.facility) AS 'Fac', (SELECT COUNT(T.attribute_6) FROM survey_tokens_693553 T WHERE T.attribute_6 = R.`FacilityNumber` AND T.usesleft = 0) AS 'completed', (SELECT COUNT(T.attribute_6) FROM survey_tokens_693553 T WHERE T.attribute_6 = R.`FacilityNumber`) AS 'total' FROM (SELECT `FacilityNumber`, Facility FROM survey_report_693553 limit 5) AS R ) AS K order by 2 ASC limit 5;
I was not able to create a temp table in Grafana so I created these tables with the data for demo purposes:
DROP TABLE IF EXISTS temp.completed_facility_693553; CREATE TABLE IF NOT EXISTS temp.completed_facility_693553 SELECT attribute_6 AS 'facility_number', COUNT(attribute_6) AS 'Count' FROM survey_tokens_693553 WHERE usesleft = 0 group by 1; select * from temp.completed_facility_693553; DROP TABLE IF EXISTS temp.tmp_total_facility_693553; CREATE TABLE IF NOT EXISTS temp.tmp_total_facility_693553 SELECT attribute_6 AS 'facility_number', COUNT(attribute_6) AS 'Count' FROM survey_tokens_693553 group by 1; select * from temp.tmp_total_facility_693553;
Then I do this in Grafana. It works but I think I can do it with multiple queries if I can find documentation.
SELECT UNIX_TIMESTAMP(datestamp) as 'time_sec', (C.Count / T.Count) * 100 as 'value', Facility as 'metric' FROM survey_report_693553 R JOIN temp.completed_facility_693553 C on C.facility_number = R.FacilityNumber JOIN temp.tmp_total_facility_693553 T on T.facility_number = C.facility_number WHERE $__timeFilter(datestamp) group by 1, 3 order by 2 $HighestLowest LIMIT 10;
$__name | value Facility_1 | 53.33 Facility_2 | 57.14 Facility_3 | 66.67 Facility_4 | 83.33 Facility_5 | 100.00