Distribution query

Probes the distribution of responses across possible values. Use it, for example, to calculate the percentage of people answering 9 or 10 out of 10. It can also be used to calculate NPS (Net Promoter) scores.

This kind of query does not have a counterpart in SQL. The power of the distribution query is to give the number of records with each value for many columns in just one query. Obtaining these values using regular aggregation queries would require one query per column.

The special marker distribution_value has to be present once and only once among the requested "columns" in the select part of the query. In this column in the result table will appear every value that any of the fields with the dist aggregation function has. So let's say you have a column A with values 1-5 and a column B with values 1-10, then select distribution_value, dist(A), dist(B) from survey will result in 10 rows, for the values 1-10, but the column dist(A) will only have values for the first 5 rows.

You can also add columns without any aggregation function to the query. This will split the query in the same way as with the aggregation queries. So you can add responsedate.year_and_month to see how the distribution has changed over time, month by month.

Note that you cannot use the aggregation functions avg/sum/count in a distribution query, they only apply to aggregation queries.

Query URL/MQL Client command

MQL Client command

select distribution_value, columnname, columnname, columnname,…, dist(columnname), dist(columnname), dist(columnname)… from tablename [where …] [order by columnname, columnname, … [asc|desc]] [limit n]

Query URL

https://api.medallia.com/yourompany?user=username&pass=password&query=select+tablename.columnname%2C+distribution_value%2C+%2C+dist%28columnname%29%2C+dist% columnname%29%2C+dist% columnname%29…+from+tablename+order+by+tablename.columnname&output=csv|json&version=1&apikey=value

Query parameters

distribution_value: Special marker used for distribution queries only.
columnname: Any of the columns available in the table.
tablename: Any of the tables available in the system.
dist: Calculates the distribution
where: Filters the results. See Filtering.
order by: Sorts the results. See Sorting and limiting.
asc|desc: Ascending (ASC) or descending (DEC) order
limit n: Limits results to max n rows. This is typically not useful for distribution queries, but is included for completeness. See Sorting and limiting.

Sample requests and responses

Sample MQL Client command

select responsedate.year, distribution_value, dist(br_overall_experience), dist(br_likely_to_recommend), dist(br_likely_to_buy_next_prod) from survey order by responsedate.year

Sample Query URL

https://api.medallia.com/mainstreet?user=john&pass=Ypass12&query=select+responsedate.year%2C+distribution_value%2C+dist%28br_overall_experience%29%2C+dist%28br_likely_to_recommend%29%2C+dist%28br_likely_to_buy_next_prod%29+from+survey+order+by+responsedate.year&output=csv&version=1&apikey=5hxf4qz2qzjuxm46686csnwk

Sample Response (CSV)

 responsedate.year | distribution_value | dist(br_overall_experience) | dist(br_likely_to_recommend) | dist(br_likely_to_buy_next_prod) |
 2005              | 1                  | 750.0                       | 240.0                        | 707.0                            |
 2005              | 2                  | 552.0                       | 449.0                        | 567.0                            |
 2005              | 3                  | 850.0                       | 551.0                        | 893.0                            |
 2005              | 4                  | 1233.0                      | 847.0                        | 1124.0                           |
 2005              | 5                  | 1638.0                      | 1183.0                       | 1574.0                           |
 2005              | 6                  | 1803.0                      | 1524.0                       | 1832.0                           |
 2005              | 7                  | 2064.0                      | 1831.0                       | 2118.0                           |
 2005              | 8                  | 2155.0                      | 2087.0                       | 2099.0                           |
 2005              | 9                  | 2397.0                      | 2075.0                       | 2413.0                           |
 2005              | 10                 | 6027.0                      | 8682.0                       | 6142.0                           |
 2006              | 1                  | 685.0                       | 228.0                        | 714.0                            |
 2006              | 2                  | 608.0                       | 431.0                        | 578.0                            |
 2006              | 3                  | 886.0                       | 582.0                        | 882.0                            |
 2006              | 4                  | 1234.0                      | 820.0                        | 1191.0                           |
 2006              | 5                  | 1585.0                      | 1189.0                       | 1625.0                           |
 2006              | 6                  | 1863.0                      | 1575.0                       | 1888.0                           |
 2006              | 7                  | 2001.0                      | 1838.0                       | 2065.0                           |
 2006              | 8                  | 2134.0                      | 2028.0                       | 2159.0                           |
 2006              | 9                  | 2487.0                      | 2149.0                       | 2420.0                           |
 2006              | 10                 | 6047.0                      | 8690.0                       | 6008.0                           |