---
agentTools:
  projectIndex: https://docs.thinknum.com/llms.txt
---

# Group/Aggregation

[block:api-header]
{
  "type": "basic",
  "title": "Aggregating Queries"
}
[/block]

Sometimes you only need get aggregated results for a dataset. In such cases you must specify how to group and aggregate the results through the "groups" and "aggregates" features.

The groups are set with only one key:

[block:parameters]
{
  "data": {
    "h-0": "Key",
    "h-1": "Description",
    "0-0": "column",
    "0-1": "The column the grouping should be applied on"
  },
  "cols": 2,
  "rows": 1
}
[/block]

An aggregation has the following required keys:

[block:parameters]
{
  "data": {
    "h-0": "Key",
    "h-1": "Description",
    "0-0": "column",
    "1-0": "type",
    "0-1": "The column the aggregation should be applied to",
    "1-1": "The type of \"aggregation\" to do: \"count\", \"average\", \"max\", \"min\", \"sum\""
  },
  "cols": 2,
  "rows": 2
}
[/block]

Not every column will support every aggregation type.

For example, to get a count of Apple stores by city and state, an example request is:

[block:code]
{
  "codes": [
    {
      "code": "POST /dataset/store/query/ HTTP/1.1\nAuthorization: token 01234567890123456789\nX-API-Version: 20151130\n\n{\n    \"tickers\": [\"nasdaq:aapl\"],\n    \"groups\": [\n        {\n            \"column\": \"city\"\n        },\n        {\n            \"column\": \"state\"\n        }\n    ],\n    \"aggregations\": [\n        {\n            \"column\": \"city\",\n            \"type\": \"count\"\n        }\n    ]\n}",
      "language": "http"
    },
    {
      "code": "curl -X POST \"https://data.thinknum.com/datasets/store/query/\" \\\n-H \"Accept: application/json\" \\\n-H \"X-API-Version: 20151130\" \\\n-H \"Authorization: token 01234567890123456789\" \\\n-d $'{\n    \"tickers\": [\"nasdaq:aapl\"],\n    \"groups\": [\n        {\n            \"column\": \"city\"\n        },\n        {\n            \"column\": \"state\"\n        }\n    ],\n    \"aggregations\": [\n        {\n            \"column\": \"city\",\n            \"type\": \"count\"\n        }\n    ]\n}'",
      "language": "curl"
    },
    {
      "code": "response = requests.post(\n    url='https://data.thinknum.com/datasets/store/query/', \n    headers={\n        'Authorization': 'token 01234567890123456789', \n        'X-API-Version': '20151130', \n        'Accept': 'application/json'\n    }, \n    data=json.dumps({\n        \"tickers\": [\"nasdaq:aapl\"],\n        \"groups\": [\n            {\n                \"column\": \"city\"\n            },\n            {\n                \"column\": \"state\"\n            }\n        ],\n        \"aggregations\": [\n            {\n                \"column\": \"city\",\n                \"type\": \"count\"\n            }\n        ]\n    })\n)\nresults = json.loads(response.text)",
      "language": "python"
    }
  ]
}
[/block]

A sample response follows:

[block:code]
{
  "codes": [
    {
      "code": "{\n    \"state\": \"complete\",\n    \"total\": 6903,\n    \"id\": \"bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57\",\n    \"formats\": [\n        \"application/vnd.thinknum.table+json\",\n        \"application/vnd.thinknum.chart+json\"\n    ]\n}",
      "language": "json"
    }
  ]
}
[/block]

To check if query is completed:

[block:code]
{
  "codes": [
    {
      "code": "HEAD /datasets/store/query/bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57 HTTP/1.1\nAuthorization: token 01234567890123456789\nX-API-Version: 20151130\nAccept: application/vnd.thinknum.table+json",
      "language": "http"
    },
    {
      "code": "curl -I HEAD \"https://data.thinknum.com/datasets/store/query/bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57\" \\\n-H \"Accept: application/vnd.thinknum.table+json\" \\\n-H \"X-API-Version: 20151130\" \\\n-H \"Authorization: token 01234567890123456789\"",
      "language": "curl"
    },
    {
      "code": "response = requests.head(\n    url='https://data.thinknum.com/datasets/store/query/bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57', \n    headers={\n        'Authorization': 'token 01234567890123456789', \n        'X-API-Version': '20151130', \n        'Accept': 'application/vnd.thinknum.table+json'\n    }\n)\nresults = dict(response.headers)",
      "language": "python"
    }
  ]
}
[/block]

A sample response follows:

[block:code]
{
  "codes": [
    {
      "code": "HTTP/1.1 200 OK\nServer: nginx\nDate: Wed, 28 Apr 2021 15:48:33 GMT\nContent-Type: application/json\nConnection: keep-alive\nX-Truncated: false\nX-Formats: application/vnd.thinknum.table+json, application/vnd.thinknum.chart+json, text/csv, application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\nX-Total: 6903\nX-State: complete\nStrict-Transport-Security: max-age=15768000",
      "language": "json"
    }
  ]
}
[/block]

To retrieve data:

[block:code]
{
  "codes": [
    {
      "code": "GET /datasets/store/query/bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57?limit=10&start=1 HTTP/1.1\nAuthorization: token 01234567890123456789\nX-API-Version: 20151130\nAccept: application/vnd.thinknum.table+json\nContent-Type: application/json",
      "language": "http"
    },
    {
      "code": "curl -X GET \"https://data.thinknum.com/datasets/store/query/bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57?limit=10&start=1\" \\\n-H \"Accept: application/vnd.thinknum.table+json\" \\\n-H \"Content-Type: application/json\" \\\n-H \"X-API-Version: 20151130\" \\\n-H \"Authorization: token 01234567890123456789\"",
      "language": "curl"
    },
    {
      "code": "response = requests.get(\n    url='https://data.thinknum.com/datasets/store/query/bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57', \n    headers={\n        'Authorization': 'token 01234567890123456789', \n        'X-API-Version': '20151130', \n        'Accept': 'application/vnd.thinknum.table+json'\n    }\n)\nresults = json.loads(response.text)",
      "language": "python"
    }
  ]
}
[/block]

A sample response follows:

[block:code]
{
  "codes": [
    {
      "code": "{\n    \"group_fields\": [\n    ...\n    ],\n    \"total\": 6903,\n    \"id\": \"bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57\",\n    \"count\": 10,\n    \"rows\": [\n        [\n            \"CLINTON\",\n            \"OK\",\n            1\n        ],\n        [\n            \"Woodbridge\",\n            \"Virginia\",\n            1\n        ],\n        ...\n    ],\n    \"fields\": [\n        {\n            \"display_name\": \"City\",\n            \"format\": null,\n            \"metric\": false,\n            \"id\": \"city\",\n            \"length\": 64,\n            \"summary\": \"\",\n            \"type\": \"string\",\n            \"options\": []\n        },\n        {\n            \"display_name\": \"State\",\n            \"format\": null,\n            \"metric\": false,\n            \"id\": \"state\",\n            \"length\": 64,\n            \"summary\": \"\",\n            \"type\": \"string\",\n            \"options\": []\n        },\n        {\n            \"display_name\": \"City (Count)\",\n            \"format\": null,\n            \"metric\": true,\n            \"id\": \"city@count\",\n            \"length\": null,\n            \"summary\": \"\",\n            \"type\": \"number\",\n            \"options\": []\n        }\n    ],\n    \"sort_fields\": [],\n    \"start\": 1,\n    \"state\": \"complete\",\n    \"limit\": 10,\n    \"last_date_updated\": \"2021-04-01T02:34:56Z\"\n}",
      "language": "json"
    }
  ]
}
[/block]