Aggregation query

Includes such queries as retrieving average scores per month for the past year, much like the web UI’s Satisfaction tab.

There are two ways that a column can be used in the select part of an aggregation query:

The column name by itself. This is a split, similar to the group by functionality in the SQL world (but you don't have to include the group by clause at the end, we infer the split automatically).
An aggregation function around the column name. In this case we will show the aggregated (typically average, can also be sum or count) value of the column.

So in a query like select A, avg(B) from survey the resulting table will contain one row for each value stored in the column A, and in that row we will display the average value of the column B for all the surveys that had this particular value in column A.

Query URL/MQL Client command

MQL Client command

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

Query URL

https://api.medallia.com/yourcompany?user=username&pass=password&query=select+tablename.columnname%2C+count%28*%29%2C+aggregate_function%28columnname%29%2C+aggregate_function %columnname%29%2C+aggregate_function %columnname%29…+from+tablename&output=csv|json&version=1&apikey=value

Query parameters

columnname

Any of the columns available in the table. Note however that columns with encoding TEXT cannot be used in aggregation queries.

aggregate_function

Includes:

avg(ov_satisfaction) returns average overall satisfaction score
sum(ov_satisfaction) returns the sum of the overall satisfaction score for everybody who answered the question
count(ov_satisfaction) returns the count of surveys with an answer to the overall satisfaction question
count(*) returns

The difference between count(*) and count(ov_satisfaction) is that count(*) yields number of surveys, whereas count(ov_satisfaction) yields the number of surveys with a value for the ov_satisfaction field

Note that some columns are inherently aggregations, like e.g. an NPS column, and in this case you will always get the NPS value independently of whether you use the avg, sum or count function.

For normal columns however you will have that:

count(ov_satisfaction) * avg(ov_satisfaction) = sum(ov_satisfaction)

tablename

Any of the tables available in the system.

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 result table to max n rows. See Sorting and limiting.

Sample requests and responses

Sample MQL Client command

select responsedate.year_and_month, count(*), avg(br_overall_experience), avg(br_likely_to_recommend), avg(br_likely_to_buy_next_prod) from survey

Sample Query URL

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

Sample response (CSV)

 responsedate.year_and_month | count(*) | avg(br_overall_experience) | avg(br_likely_to_recommend) | avg(br_likely_to_buy_next_prod) |
 2005-01                     | 1668.0   | 6.419064748201439          | 7.514388489208633           | 6.537170263788969               |
 2005-02                     | 1516.0   | 6.912269129287599          | 7.705145118733509           | 6.966358839050132               |
 2005-03                     | 1645.0   | 7.189665653495441          | 7.854103343465046           | 7.236474164133739               |
 2005-04                     | 1655.0   | 7.296676737160121          | 8.035045317220543           | 7.40785498489426                |
 2005-05                     | 1625.0   | 7.445538461538462          | 8.07876923076923            | 7.496                           |
 2005-06                     | 1538.0   | 7.788036410923277          | 8.491547464239272           | 7.760728218465539               |
 2005-07                     | 1645.0   | 7.782370820668693          | 8.450455927051673           | 7.785410334346505               |
 2005-08                     | 1654.0   | 7.585247883917775          | 8.39056831922612            | 7.71825876662636                |
 2005-09                     | 1628.0   | 7.578009828009828          | 8.18980343980344            | 7.584766584766585               |
 2005-10                     | 1652.0   | 7.403147699757869          | 8.168280871670703           | 7.3541162227602905              |
 2005-11                     | 1576.0   | 7.00507614213198           | 7.688451776649746           | 7.091370558375634               |
 2005-12                     | 1667.0   | 7.0509898020395925         | 7.739652069586083           | 6.983203359328134               |
 2006-01                     | 1661.0   | 6.397350993377484          | 7.379289584587598           | 6.377483443708609               |
 2006-02                     | 1549.0   | 7.046481601032925          | 7.773402194964493           | 6.970303421562298               |
 2006-03                     | 1718.0   | 7.155413271245634          | 7.927823050058207           | 7.054714784633295               |
 2006-04                     | 1534.0   | 7.353976531942633          | 8.026075619295959           | 7.32464146023468                |
 2006-05                     | 1694.0   | 7.465761511216057          | 8.174734356552538           | 7.537780401416765               |
 2006-06                     | 1595.0   | 7.7065830721003135         | 8.390595611285267           | 7.721003134796239               |
 2006-07                     | 1662.0   | 7.7328519855595665         | 8.45006016847172            | 7.7539109506618535              |
 2006-08                     | 1676.0   | 7.664677804295943          | 8.30071599045346            | 7.658711217183771               |
 2006-09                     | 1562.0   | 7.613956466069142          | 8.352112676056338           | 7.555697823303457               |
 2006-10                     | 1653.0   | 7.384150030248034          | 8.022383545069571           | 7.37870538415003                |
 2006-11                     | 1640.0   | 7.076219512195122          | 7.864634146341463           | 7.038414634146341               |
 2006-12                     | 1586.0   | 6.98234552332913           | 7.704918032786885           | 7.077553593947036

Sample MQL Client command

SELECT COUNT(*) FROM SURVEY

Sample Query URL

https://api.medallia.com/mainstreet?user=john&pass=Ypass12&query=SELECT+COUNT%28*%29+FROM+SURVEY&output=csv&version=1&apikey=5hxf4qz2qzjuxm46686csnwk

Sample response (CSV)

COUNT(*)
 38999.0