# Percent of Total
> A detailed walkthrough on how to calculate the distribution of a metric using AQL.
:::info Alternative
This is the AQL-centric approach to calculate Percent of Total.
Holistics also provides a UI-friendly solution: [Percent of Total](/docs/percent-of-total)
:::
## Introduction
The Percent of Total analysis is a commonly used reporting technique that helps understand the contribution of each row value to the overall population.
In SQL, you have to divide your calculation into multiple CTEs (Common Table Expressions) and then cross-join those together to perform Percent of Total. Whereas with AQL, you can achieve this calculation in a more concise and reusable way by focusing on the business logic rather than the technical semantics.
:::tip Recommended Prerequisites
- [of_all](/reference/aql/of_all): Exclude your metric from being evaluated against certain dimensions/grains.
:::
## Video Tutorial
## High-level Flow
Suppose we want to see how each product's sales contribute to global sales.
To illustrate the calculation using AQL, we'll explore two different ways of performing it. First, we will break down the calculation into manageable steps or components. Then, we'll demonstrate how to write it as a single-line query.
## Setup
To make it simple, we'll be working on the `order_items` modeling definition only with the following fields.
:::tip Measures and Metrics
You may see the use of `measure` in code snippets throughout this article. In Holistics, metrics defined inside data models are syntactically referred to as `measure`. We will use the word "metric" outside of code snippets to avoid confusions. [Read more about Metrics here](/as-code/aql/learn/defining-a-metric)
:::
We'll show two equivalent ways to define `percent_of_total`. Pick whichever style suits the metric:
```aml
Model order_items {
...
dimension id {...}
dimension product {...}
dimension amount {...}
measure total_sales {
label: 'Total Sales'
type: 'number'
definition: @aql sum(order_items.amount) ;;
}
measure _total_sales_of_all {
label: 'Total Sales of All'
definition: @aql sum(order_items.amount) | of_all(order_items.id) ;;
hidden: true
}
measure percent_of_total {
label: "Percent of Total"
type: 'number'
definition: @aql (order_items.total_sales*1.0) / order_items._total_sales_of_all ;;
}
}
```
```aml
Model order_items {
...
dimension id {...}
dimension product {...}
dimension amount {...}
measure percent_of_total {
label: "Percent of Total"
type: 'number'
definition: @aql (sum(order_items.amount) * 1.0) / (sum(order_items.amount) | of_all(order_items));;
}
}
```
## Implementation
**1. Define the aggregation**
Calculate the total sales value for each product by defining the metric `total_sales`
```aml
Model order_items {
...
measure total_sales {
label: 'Total Sales'
type: 'number'
definition: @aql sum(order_items.amount) ;;
}
}
```
export const step_1_sql = `SELECT
products.name as "products->name"
sum(order_items.amount) as "order_items->total_sales"
FROM
order_items
LEFT JOIN
products
ON order_items.product_id = products.id
GROUP BY 1
`
export const step_1 = {
"fields": [
"Product",
"Total Sales"
],
"records": [
[
"Chocolate 2",
"2187"
],
[
"Samsung Tablet 2",
"101313"
],
[
"H&M T-shirt",
"25194"
],
[
"Face Treatment",
"4743"
],
[
"Adidas Shoes",
"38901"
],
[
"Xbox",
"102797"
],
[
"Jordan Shoes",
"18760"
],
[
"Bunk Bed",
"13893"
],
[
"AFC Biscuits",
"2350"
],
[
"Sony Headphone 1",
"10665"
],
[
"Sofa 1",
"50334"
],
[
"Body Scrub",
"211359"
],
[
"Flour 1",
"800"
],
[
"Converse Shoes",
"40575"
],
[
"Outdoor Sofa",
"24999"
],
[
"Bathroom Mirror",
"3960"
],
[
"Face Moisturizers",
"4576"
],
[
"Wii",
"24090"
],
[
"Cookies 2",
"6049"
],
[
"Face Cream",
"17365"
],
[
"Nokia C1",
"87307"
],
[
"High Cabinet",
"8432"
],
[
"Lacoste T-shirt",
"21994"
],
[
"Body Cleanser",
"161143"
],
[
"Reebox Shoes",
"21231"
],
[
"Ticwatch 1",
"74019"
],
[
"KitKat 1",
"1037"
],
],
"metadata": {
"aql": "explore {\n dimensions {\n products.name\n }\n measures {\n order_items.sales\n }\n}",
"executed_sql": "WITH \"aql__t3\" AS (\n SELECT\n (\"order_items\".\"quantity\" * \"products\".\"price\") AS \"value\",\n \"order_items\".\"product_id\" AS \"product_id\"\n FROM\n \"demo\".\"order_items\" \"order_items\"\n LEFT JOIN \"demo\".\"products\" \"products\" ON \"order_items\".\"product_id\" = \"products\".\"id\"\n)\nSELECT\n \"products\".\"name\" AS \"products->name\",\n SUM(\"order_items\".\"value\") AS \"order_items->sales\"\nFROM\n \"aql__t3\" \"order_items\"\n LEFT JOIN \"demo\".\"products\" \"products\" ON \"order_items\".\"product_id\" = \"products\".\"id\"\nGROUP BY\n 1\n",
"url": "https://play.amql.org/#code/uDritRdcsbtfekV2DrisS2OsH2FriisE2HtiVcRkZ2YtjYZgjsGxkDxYDtCxULrgtUVdfPTfl23tiZVjxKD2RtPxUGtZ2kxRFxbEuBxZF3ptMe3utkuBtgVsSrgntdRe2Wtk2o3LtVrnxXEtWifd2Xse20tcU3ktWxrG2B3M23tcxuDyRGxpFxrDxlExVEyfLxbFuFyjFtfyKbzvIyNRyFKxDD2C0tStg4mtUlTk5btZU0qGtfi2Hti2Otk2M2UxdHxcD0t7yULzlPyiK0wmzyIyKPyCK0tXzIF0rWxMH0p90pZygI0nmzsG0lwtlj0kR2PxVE0e9zTWyaH0YmzfF0XwtTZ2QtV0YK0mGxVD5D0X9zSUyZH0WmzeG0XarisErisHtmynD0WDsGtRdcrisSritT0gDtkrgsZlktPGsdtCDshnmrgsV2ysKGIrnxrEtU6DtR4ZtkjsHxpDxQNxJHyHHscxOG1nGtV3Vtdd3mtTVziGxqEtPjflxRDtPe2v1gExnEzYDxeE0BFtj2UtLz4G0IGsExNH21tekixQKtfi2sti6Wtk3BxSNxNItgi3PtlT4ixPHtl4X2bxLEtNytGti3r4Q1DDtjYZgy0KxWMsA0IFy5JxKD2RtPxUGzdFxbHuBxZFsErg2KtlVsBywHyKSzUI42tUyHG0HLxdIxcDyKg0rGyIVxMHyGh1VFyFO2PxVEx8j1FHyAIyOGxVD6Qx8Jx7EtN2FuFrisErisH2j2ntcjsHrisSrixOLz6LsGxTFsGtRdcxjDslxMErgxeLrguDyPE3ftgVsSrgntkRScVrnxRE4atS2oxSDsnxqKxYF2OtjTi4vz3ExeDxTG5ItRPjfliTVPe3pyLE0BExcFtfne3CxRHsYtYfcZj26tT4Y3BxeFyyFx0ItMrixlE2GsG2DzlL2NxqDypFtZ2m6wy6Drgtgi5NtlTk1ZD0FGzzKtAxaGrgshtUyiF0gJteld2htixUHtY243h0GDtWR6ztVxTG0eTxVEtWZe4KxUFsYtjhc3wuDrgjtDsntFCsbdGzJLuFFsTTxxEuFztQ0OHzrRsnxYEzp9zplzFJznZthlRe284GznQtBxYHzn9znizFJznZtm3htlVzkQtGxVEzhby2NtRy2D1VHtkVdjy3KsCrgtgifUlTk2U2JtZT3pzJN4HtRjli2Vtj3v5fzHQtEfk2XrgtDxbEzNs3UtdsAzRM0mFsBy7MuFrisErisHtd4c3ptjsHtTR2ltX2vtZ3nsGxSFsGtRdcrisSrislxMErgxdKziGzGH4dtS4azFFzxIsbxpJxXF28tjT2NtgzbGrnxTG2itRPjf526PtPe3nyKE2m31xcFtfne40xRHsYtYfcZj26tT4W3BxeFyxFx0ItMrixlE2GsG2DzjK2MxpDyoFtZ2ltejy5DrgtZUz4GzpKshtUyRF0OJte6z2ZtixUHtY2w3bzyDtW64tjVxTG0NTxVEtWZeZxUGsYtjh6uuDDrgjtDsntFCsbd4132uFFsTTxpEuFzVQ0HEzXQsmxUDzZO5ZuAtkzX9zXOy4FzZZtgRi2KtkP11StAxZFrg189zlkzHKzqNuFrisErisHtdf263dsHtTx7D3asGxOFsG5XtcrisSrislxMErgxZG0DGzmHtk5ItcVy6F0SIsbxlFxTFzjTtUR2ytPjfliTVP12ExYD2m3txcFtfne50xRHsYtYfcZj26tT4O3BxeFytFx0ItMrixlE2GsG2DzbG2IxlDykFtZ2htejy1Drg0sDzwFzhKshtU1tH0GHteld2ZtixUHtY2w3XzuDtWR6PtVxTG0JTxVEtWZey3DxUEsYtjh6iuDDrgjtDsntFCsbd4x32uFFsTTxpEuFzVQ0DEzXQsmxUDzZOtkVuAtkzX9zXOy4FzZZtTflekiuBtP2I3HzhQsbxcGrg2I2czp9zpgzLNzxNuFrisErisHtdxXD3gsHxmGtZ3gsGxRFsG5htcrisSrislxME0QHxcD0NGztI5Stc0DG0cOxoDxWFzvTtUR21tPj2mtiTVPe3lyJE2m3zxcFtfneVixRHsYtYfcZj26tT4U3BxeFywFx0ItMrixlE2GsG2DzhJ2LxoDynFtZ2ktejy4D0AD06Dz4FzpM2UyUF0QKtVuAtkxSHtYZU2ezzDtWR6dtVxTG0OTxVEtWZeZxUGsYtjh6tuDDrgjtDsntFCsbdGy4FuFFsTTxrEuFzZQ0KEzZQsmxUDzZ9zZgy4FzZZ43te23te2NtkPzjU68xeHrgzt9ztkzPPz3NuFrisErisHtdf3C3isHtfi2HtijsGxOFsG2qtcrisSrislxMErgxZG0LGzpI5Qtcz8G0aIsnxlFxTFzoTtUR2ytPjfliTVzMFxYD2m3txcFtfn4jtixRHsYtYfcZj26tT4O3BxeFytFx0ItMrixlE2GsG2DzbG2IxlDykFtZ2htejy1DrgtZUzwGzhKshtU1yH0GHteld2ZtixUHtY2w3MzuDtWR6PtVxTG0JTxVEtWZeZxUGsYtjh6iuDDrgjtDsntFCsbd4x32uFFsTTxpEuFzVQ5W6HtljzZQtDxWFzdOtkVuAtkzb9zbOy8HzfZ3RtV2htVUP2FzjQsbxaGrgsZzVIznHtUxWD3v3Dzr9zrOzLLzvZ5OtViP3vzsQtFxXDrgshtUzpOteld2etizn9znOzDIzkZ2K3Vtfle5JzlPscxYHzm9zmizFJzna4VtZm33uBtPRkkVdgjzvRxgHrgsZxgGz39z3jzVQ0CNuFrisErisHtdfxlDtjsHtgi2ItlTkjsGxQFsGtRdcrisSrislxMErgxbI0VGzxHtk5atcVzIF0kItAxnHxVFzwTtU4KtRPjfliTVPe3jyIE2m3xxcFtfne4sxRHsYtYf47tj26tT4S3BxeFyvFx0ItMrixlE2GsG2DzfI2KxnDymFtZ2jtejy3DrgtZUz0GzlKshtU18H0KHteld2ZtixUHtY2w3ZzwDtWR6XtVxTG0LTxVEtWZeZxUGsYtjh6ouDDrgjtDsntFCsbd4z32uFFsTTxpEuFzVQtT6ItVXfiuBtPzeSsbxbHrgzn9znkzLMzwZteR2KzpQsmxUDziO3MuAtkzg9zgOy4FzZZ2ItiTYRek0BEzgPslxbHrgshtUznOteld2itizp9zpOzLMzwZtg3WtT5SzqPtAxVEzk9zkiy8GzeZ3QtVRkVUP2FzjQsbxaGrgsZtkzoOtUxWD3u3Dzq9zqOzLLzvNuFrisErisHtdf273esHtl3htijsGxNFsGtRdcrisSrislxMErgxYF0FGznHtk5KtczkG0UItFxkExSFzhT0ZDtRPjfli6ztPe3d0sFtV3rxcFtfne26xRHsYtYfcZj26tT4M3BxeFysFx0ItMrixlE2GsG2DzZF2HxkDyjF14Dtejy0Drg62zuGzfKshtU1qH0EHteld2ZtixUHtY2w3WztDtWR6LtVxTG0ITxVEtWZeZxUGsYtjh6fuDDrgjtDsntFCsbd4w32uFFsTTxpEuFzVQ2FtXePlgP1NDtVzfQtDxcDrgtFgrgscxcDzpOxuEzn9znOzLNzxh2lzvYsZtkztS313Dzx9zxWzLDzvZ3BtijkPeR2QzvQsexaErgxaEzvO4tuA0EIzr9zrHzHLzrZ4ZzqXskxZDzp9zplzFKzoZtVdRZx0DzkOsdxVEzg9zggy6GzcZtSZikYPUR35zhQsaxaE2qxaDzmOxSLzm9zmHzHLzrZtX2I3GtiznQsfxWFzjO3JuAtkzj9zjOy8HzfZtT24uBtP3rzgQsbxXDrg2XzhOteld2ez1Izj9zjHzDIzkZtWlccPeR2PzmQsexZDrgxZEzoOtkVuAtkzm9zmOzFKzoNuFrisErisHtdf263d2H6dtTYRekjsGxRFsG2ttcrisSrislxMErgxcJ0HGzqI5Mtc0AG0WIslxoIxWFzsTtUR21tPjfl2ntVzVFxYD2m3zxcFtfne3CxRHsYtYfcZj26tT4U3BxeFywFx0ItMrixlE2GsG2DzhJ2LxoDynFtZ2Utejy4DrgtZUz2GznKshtU15H0MHteld2ZtixUHtY2w3azxDtWR6btVxTG0MTxVEtWZeZxUGsYtjh6ruDDrgjtDsntFCsbd4032uFFsTTxpEuFzVQ0GEzXQsmxUDzZOtkVuAtkzX9zXOy4FzZZtT22uBtP1zSsbxXDrg149zhkzDIzkNuFriuFsEritR2psb2y6W2DrisSri6ctgcfi5OzqF0EJtjz6ItgifUlTkj0zFyKH2F2LtRjl21xmJ3E4Qti3w3Wtd2ptj462ZxqH3y3xykHsctRk20tVksmyKDy5D2vtdfPVTfd2OtiT2R2mtT3S2k4XsetZ3AtA2qtYxjDsH2i2m6LsHyfMxTFsG3Gtc5Q"
}
}
export const step_2 = {
"fields": [
"Product",
"Total Sales",
"Total Sales of All"
],
"records": [
[
"Chocolate 2",
"2187",
"4391797"
],
[
"Samsung Tablet 2",
"101313",
"4391797"
],
[
"H&M T-shirt",
"25194",
"4391797"
],
[
"Face Treatment",
"4743",
"4391797"
],
[
"Adidas Shoes",
"38901",
"4391797"
],
[
"Xbox",
"102797",
"4391797"
],
[
"Jordan Shoes",
"18760",
"4391797"
],
[
"Bunk Bed",
"13893",
"4391797"
],
[
"AFC Biscuits",
"2350",
"4391797"
],
[
"Sony Headphone 1",
"10665",
"4391797"
],
[
"Sofa 1",
"50334",
"4391797"
],
[
"Body Scrub",
"211359",
"4391797"
],
[
"Flour 1",
"800",
"4391797"
],
[
"Converse Shoes",
"40575",
"4391797"
],
[
"Outdoor Sofa",
"24999",
"4391797"
],
[
"Bathroom Mirror",
"3960",
"4391797"
],
[
"Face Moisturizers",
"4576",
"4391797"
],
[
"Wii",
"24090",
"4391797"
],
[
"Cookies 2",
"6049",
"4391797"
],
[
"Face Cream",
"17365",
"4391797"
],
[
"Nokia C1",
"87307",
"4391797"
],
[
"High Cabinet",
"8432",
"4391797"
],
[
"Lacoste T-shirt",
"21994",
"4391797"
],
[
"Body Cleanser",
"161143",
"4391797"
],
[
"Reebox Shoes",
"21231",
"4391797"
],
[
"Ticwatch 1",
"74019",
"4391797"
],
[
"KitKat 1",
"1037",
"4391797"
],
[
"Umbrella",
"29855",
"4391797"
],
[
"Guess Jeans",
"13881",
"4391797"
],
[
"10 Deep T-shirt",
"9398",
"4391797"
],
[
"iPad 1",
"130244",
"4391797"
]
],
"metadata": {
"aql": "explore {\n dimensions {\n products.name\n }\n measures {\n order_items.total_sales,\n order_items._total_sales_of_all\n }\n}",
"executed_sql": "WITH \"aql__t3\" AS (\n SELECT\n (\"order_items\".\"quantity\" * \"products\".\"price\") AS \"amount\",\n \"order_items\".\"product_id\" AS \"product_id\"\n FROM\n \"demo\".\"order_items\" \"order_items\"\n LEFT JOIN \"demo\".\"products\" \"products\" ON \"order_items\".\"product_id\" = \"products\".\"id\"\n), \"aql__t1\" AS (\n SELECT\n \"products\".\"name\" AS \"products->name\",\n SUM(\"order_items\".\"amount\") AS \"sum_order_items->amount\"\n FROM\n \"aql__t3\" \"order_items\"\n LEFT JOIN \"demo\".\"products\" \"products\" ON \"order_items\".\"product_id\" = \"products\".\"id\"\n GROUP BY\n 1\n), \"aql__t4\" AS (\n SELECT\n SUM(\"order_items\".\"amount\") AS \"sum_order_items->amount\"\n FROM\n \"aql__t3\" \"order_items\"\n)\nSELECT\n \"aql__t1\".\"products->name\" AS \"products->name\",\n \"aql__t1\".\"sum_order_items->amount\" AS \"order_items->total_sales\",\n \"aql__t4\".\"sum_order_items->amount\" AS \"order_items->_total_sales_of_all\"\nFROM\n \"aql__t1\"\n CROSS JOIN \"aql__t4\"\n",
"url": "https://play.amql.org/#code/uDritRdcsbtfekV2DrisS2OsH2FriisE2HtiVcRkZ2YtjYZgjsGxkDxYDtCxULrgtUVdfPTfl23tiZVjxKD2RtPxUGtZ2kxRFxbEuBxZF3ptMe3utkuBtgVsSrgntdRe2Wtk2o3LtVrnxXEtWifd2Xse20tcU3ktWxrG2B3M23tcxuDyRGxpFxrDxlExVEyfLxbFuFyjFtfyKbzvIyNRyFKxDD2C0tStg4mtUlTk5btZU0qGtfi2Hti2Otk2M2UxdHxcD0t7yULzlPyiK0wmzyIyKPyCK0tXzIF0rWxMH0p90pZygI0nmzsG0lwtlj0kR2PxVE0e9zTWyaH0YmzfF0XwtTZ2QtV0YK0mGxVD5D0X9zSUyZH0WmzeG0XarisErisHtmynD0WDsGtRdcrisSritT0gDtkrgsZlktPGsdtCDshnmrgsV2ysKGIrnxrEtU6DtR4ZtkjsHxpDxQNxJHyHHscxOG1nGtV3Vtdd3mtTVziGxqEtPjflxRDtPe2v1gExnEzYDxeE0BFtj2UtLz4G0IGsExNH21tekixQKtfi2sti6Wtk3BxSNxNItgi3PtlT4ixPHtl4X2bxLEtNytGti3r4Q1DDtjYZgy0KxWMsA0IFy5JxKD2RtPxUGzdFxbHuBxZFsErg2KtlVsBywHyKSzUI42tUyHG0HLxdIxcDyKg0rGyIVxMHyGh1VFyFO2PxVEx8j1FHyAIyOGxVD6Qx8Jx7EtN2FuFrisErisH2j2ntcjsHrisSrixOLz6LsGxTFsGtRdcxjDslxMErgxeLrguDyPE3ftgVsSrgntkRScVrnxRE4atS2oxSDsnxqKxYF2OtjTi4vz3ExeDxTG5ItRPjfliTVPe3pyLE0BExcFtfne3CxRHsYtYfcZj26tT4Y3BxeFyyFx0ItMrixlE2GsG2DzlL2NxqDypFtZ2m6wy6Drgtgi5NtlTk1ZD0FGzzKtAxaGrgshtUyiF0gJteld2htixUHtY243h0GDtWR6ztVxTG0eTxVEtWZe4KxUFsYtjhc3wuDrgjtDsntFCsbdGzJLuFFsTTxxEuFztQ0OHzrRsnxYEzp9zplzFJznZthlRe284GznQtBxYHzn9znizFJznZtRdfl2jzlQsZxWFzjby4NtRy4D1XHtkVdjy5KsCrgtgifUlTk2U2JtZTVrgzLN4JtRjli2Vtkfk5stPj2E5nzOQtExbErgtDxbEzTs3atdsAzXM0uGsBzFWtPzGLtPfWP2I3EzNa5ytWrgsZ2i1n3rguEziZ2azCGxdMz4NuFrisErisH2t2dtcjsHtTR2ftX2ptZ4WsGxSFyJDtcrisSrislxMErgxdK0RGzrH5MtS5JzqF0gIsbxpJxXF28tjT2Ntg0DGrnxTG2itR6m4ntiTVPe3nyKE2m31xcFtfne4uxRHsYtYfcZj26tT4W3BxeFyxFx0ItMrixlE2GsG2DzjK2MxpDyoFtZ2ltejy5DrgtZUz4GzpKshtUyRF0OJteld2ZtixUHtY2w3bzyDtWR6ftVxTG0NTxVEtWZeZxUGsYtjh6uuDDrgjtDsntFCsbd4132uFFsTTxpEuFzVQ0HEzXQsmxUDzZO5ZuAtkzX9zXOy4FzZZtgRi2KtkP11StAxZFrg189zlkzHKzqNuFrisErisHtdf263dsHtTx7D3asGxOFsG5XtcrisSrislxMErgxZG0DGzmHtk5ItcVy6F0SIsbxlFxTFzjTtUR2ytPjfliTVP12ExYD2m3txcFtfne50xRHsYtYfcZj26tT4O3BxeFytFx0ItMrixlE2GsG2DzbG2IxlDykFtZ2htejy1Drg0sDzwFzhKshtU1tH0GHteld2ZtixUHtY2w3XzuDtWR6PtVxTG0JTxVEtWZey3DxUEsYtjh6iuDDrgjtDsntFCsbd4x32uFFsTTxpEuFzVQ0DEzXQsmxUDzZOtkVuAtkzX9zXOy4FzZZtTflekiuBtP2I3HzhQsbxcGrg2I2czp9zpgzLNzxNuFrisErisHtdxXD3gsHxmGtZ3gsGxRFsG5htcrisSrislxME0QHxcD0NGztI5Stc0DG0cOxoDxWFzvTtUR21tPj2mtiTVPe3lyJE2m3zxcFtfneVixRHsYtYfcZj26tT4U3BxeFywFx0ItMrixlE2GsG2DzhJ2LxoDynFtZ2ktejy4D0AD06Dz4FzpM2UyUF0QKtVuAtkxSHtYZU2ezzDtWR6dtVxTG0OTxVEtWZeZxUGsYtjh6tuDDrgjtDsntFCsbdGy4FuFFsTTxrEuFzZQ0KEzZQsmxUDzZ9zZgy4FzZZ43te23te2NtkPzjU68xeHrgzt9ztkzPPz3NuFrisErisHtdf3C3isHtfi2HtijsGxOFsG2qtcrisSrislxMErgxZG0LGzpI5Qtcz8G0aIsnxlFxTFzoTtUR2ytPjfliTVzMFxYD2m3txcFtfn4jtixRHsYtYfcZj26tT4O3BxeFytFx0ItMrixlE2GsG2DzbG2IxlDykFtZ2htejy1DrgtZUzwGzhKshtU1yH0GHteld2ZtixUHtY2w3MzuDtWR6PtVxTG0JTxVEtWZeZxUGsYtjh6iuDDrgjtDsntFCsbd4x32uFFsTTxpEuFzVQ5W6HtljzZQtDxWFzdOtkVuAtkzb9zbOy8HzfZ3RtV2htVUP2FzjQsbxaGrgsZzVIznHtUxWD3v3Dzr9zrOzLLzvZ5OtViP3vzsQtFxXDrgshtUzpOteld2etizn9znOzDIzkZ2K3Vtfle5JzlPscxYHzm9zmizFJzna4VtZm33uBtPRkkVdgjzvRxgHrgsZxgGz39z3jzVQ0CNuFrisErisHtdfxlDtjsHtgi2ItlTkjsGxQFsGtRdcrisSrislxMErgxbI0VGzxHtk5atcVzIF0kItAxnHxVFzwTtU4KtRPjfliTVPe3jyIE2m3xxcFtfne4sxRHsYtYf47tj26tT4S3BxeFyvFx0ItMrixlE2GsG2DzfI2KxnDymFtZ2jtejy3DrgtZUz0GzlKshtU18H0KHteld2ZtixUHtY2w3ZzwDtWR6XtVxTG0LTxVEtWZeZxUGsYtjh6ouDDrgjtDsntFCsbd4z32uFFsTTxpEuFzVQtT6ItVXfiuBtPzeSsbxbHrgzn9znkzLMzwZteR2KzpQsmxUDziO3MuAtkzg9zgOy4FzZZ2ItiTYRek0BEzgPslxbHrgshtUznOteld2itizp9zpOzLMzwZtg3WtT5SzqPtAxVEzk9zkiy8GzeZ3QtVRkVUP2FzjQsbxaGrgsZtkzoOtUxWD3u3Dzq9zqOzLLzvNuFrisErisHtdf273esHtl3htijsGxNFsGtRdcrisSrislxMErgxYF0FGznHtk5KtczkG0UItFxkExSFzhT0ZDtRPjfli6ztPe3d0sFtV3rxcFtfne26xRHsYtYfcZj26tT4M3BxeFysFx0ItMrixlE2GsG2DzZF2HxkDyjF14Dtejy0Drg62zuGzfKshtU1qH0EHteld2ZtixUHtY2w3WztDtWR6LtVxTG0ITxVEtWZeZxUGsYtjh6fuDDrgjtDsntFCsbd4w32uFFsTTxpEuFzVQ2FtXePlgP1NDtVzfQtDxcDrgtFgrgscxcDzpOxuEzn9znOzLNzxh2lzvYsZtkztS313Dzx9zxWzLDzvZ3BtijkPeR2QzvQsexaErgxaEzvO4tuA0EIzr9zrHzHLzrZ4ZzqXskxZDzp9zplzFKzoZtVdRZx0DzkOsdxVEzg9zggy6GzcZtSZikYPUR35zhQsaxaE2qxaDzmOxSLzm9zmHzHLzrZtX2I3GtiznQsfxWFzjO3JuAtkzj9zjOy8HzfZtT24uBtP3rzgQsbxXDrg2XzhOteld2ez1Izj9zjHzDIzkZtWlccPeR2PzmQsexZDrgxZEzoOtkVuAtkzm9zmOzFKzoNuFrisErisHtdf263d2H6dtTYRekjsGxRFsG2ttcrisSrislxMErgxcJ0HGzqI5Mtc0AG0WIslxoIxWFzsTtUR21tPjfl2ntVzVFxYD2m3zxcFtfne3CxRHsYtYfcZj26tT4U3BxeFywFx0ItMrixlE2GsG2DzhJ2LxoDynFtZ2Utejy4DrgtZUz2GznKshtU15H0MHteld2ZtixUHtY2w3azxDtWR6btVxTG0MTxVEtWZeZxUGsYtjh6ruDDrgjtDsntFCsbd4032uFFsTTxpEuFzVQ0GEzXQsmxUDzZOtkVuAtkzX9zXOy4FzZZtT22uBtP1zSsbxXDrg149zhkzDIzkNuFriuFsEritR2psb2y6W2DrisSrisHHrg6ftgcfi5RztDxOD0KLtjxTJ2Btl4PtijsGtX2Z33tisExWJ23xPD2XtjkRk2hxWHysDxIF3ItRjl3ayIMtTfl34sAx1H4isBxyNznF2CzULzRQyGEtgifUlTk3CteR2b0hHyvOzeJ6K5ttd2ptkf4ktcPj2E2f0CDxeQtPxfLtPfWP2ItcyZH6463zTHsc5o3jtVksmy2DrisSri3AtdfPV5Vtd2OtiT2R2mtT4B2k5isetZ3HtA2qtYxjDsH2i2mtcjsHytMxTFsG3GtcriuF"
}
}
**2. Define the aggregation with coarser level-of-detail**
export const step_2_sql = `WITH aql__1 AS (
SELECT
products.name AS "products->name"
SUM(order_items.amount) AS "sum_order_items->amount"
FROM
order_items
LEFT JOIN
products
ON order_items.product_id = products.id
GROUP BY 1
),\n
aql__2 AS (
SELECT
SUM(order_items.amount) AS "sum_order_items->amount"
)\n
SELECT
aql__1."products->name" as "products->name",
aql__1."sum_order_items->amount" as "total_sales",
aql__2."sum_order_items->amount" as "_total_sales_of_all"
FROM
aql__1
CROSS JOIN aql__2
`
Total sales of all order items is also needed to be used as denominator for the percent of total. This metric can be definied by using [`of_all()`](/reference/aql/of_all) metric function.
```aml
Model order_items {
...
measure total_sales {...}
measure _total_sales_of_all {
label: 'Total Sales of All'
type: 'number'
definition: @aql order_items.total_sales | of_all(order_items) ;;
}
}
```
export const step_3_sql = `WITH
\naql__1 AS (
SELECT
SUM(amount) AS total_sales
FROM order_items
),
\naql__2 AS (
SELECT
product,
SUM(amount) AS value
FROM order_items
)
\nSELECT
aql__2.product,
aql__2.value,
(aql__2.value / aql__1.total) AS percent_per_total
FROM aql__2
CROSS JOIN aql__1
`
**3. Calculate the percent of total**
Finally, the Percent of Total is determined by dividing the `total_sales` by `_total_sales_of_all`. Since `_total_sales_of_all` remains constant, the percent of total will adjust according to `total_sales` metric and its corresponding dimension
```aml
Model order_items {
...
measure total_sales {...}
measure _total_sales_of_all {...}
measure percent_of_total {
label: "Percent of Total"
type: 'number'
definition: @aql (order_items.total_sales*1.0) / order_items._total_sales_of_all ;;
}
}
```
_To maintain the decimal format for the percent of total metric, multiplying by `1.0` explicitly casts the left side to decimal._
export const step_3 = {
"fields": [
"Product",
"Total Sales",
"Percent of Total"
],
"records": [
[
"Chocolate 2",
"2187",
"0.0498%"
],
[
"Samsung Tablet 2",
"101313",
"2.307%"
],
[
"H&M T-shirt",
"25194",
"0.5737%"
],
[
"Face Treatment",
"4743",
"0.1080%"
],
[
"Adidas Shoes",
"38901",
"0.8858%"
],
[
"Xbox",
"102797",
"2.3407%"
],
[
"Jordan Shoes",
"18760",
"0.4272%"
],
[
"Bunk Bed",
"13893",
"0.3163%"
],
[
"AFC Biscuits",
"2350",
"0.0535%"
],
[
"Sony Headphone 1",
"10665",
"0.2428%"
],
[
"Sofa 1",
"50334",
"1.1461%"
],
[
"Body Scrub",
"211359",
"4.8126%"
],
[
"Flour 1",
"800",
"0.0182%"
],
[
"Converse Shoes",
"40575",
"0.9239%"
],
[
"Outdoor Sofa",
"24999",
"0.5692%"
],
[
"Bathroom Mirror",
"3960",
"0.0902%"
],
[
"Face Moisturizers",
"4576",
"0.1042%"
],
[
"Wii",
"24090",
"0.5485%"
],
[
"Cookies 2",
"6049",
"0.1377%"
],
[
"Face Cream",
"17365",
"0.3954%"
],
[
"Nokia C1",
"87307",
"1.9879%"
],
[
"High Cabinet",
"8432",
"0.1919%"
],
[
"Lacoste T-shirt",
"21994",
"0.5008%"
],
[
"Body Cleanser",
"161143",
"3.6692%"
],
[
"Reebox Shoes",
"21231",
"0.4834%"
],
[
"Ticwatch 1",
"74019",
"1.6854%"
],
[
"KitKat 1",
"1037",
"0.2361%"
],
[
"Umbrella",
"29855",
"0.6798%"
],
[
"Guess Jeans",
"13881",
"0.3161%"
],
[
"10 Deep T-shirt",
"9398",
"0.2140%"
],
[
"Gucci T-shirt",
"7826",
"0.1782%"
],
[
"Nike Shoes",
"62529",
"1.4238%"
],
[
"Chips 2. Dried Fruit",
"5435",
"0.1238%"
],
[
"Plant Pot",
"21953",
"0.5000%"
],
[
"Sony Smart TV",
"70961",
"1.6158%"
],
[
"Wrangler Jeans",
"8562",
"0.1950%"
],
[
"Baking Soda 1",
"4536",
"0.1033%"
],
[
"iPhone X",
"72053",
"1.6406%"
],
[
"TV Stand",
"53058",
"1.2081%"
],
[
"Dining Chair 1",
"31056",
"0.7071%"
],
[
"Xbox 360",
"90142",
"2.0525%"
],
[
"Playstation 4",
"39596",
"0.9016%"
],
[
"Single Bed",
"2340",
"0.0533%"
],
[
"Oil 2",
"26525",
"0.6040%"
],
[
"Apple Watch 1",
"197375",
"4.4942%"
],
[
"Samsung Smart TV",
"72128",
"1.6423%"
],
[
"iPhone 7",
"77086",
"1.7552%"
],
[
"Kindle Fire 2",
"69054",
"1.5723%"
],
[
"Kindle Fire 1",
"203271",
"4.6284%"
],
[
"BlackBerry Key 1",
"146508",
"3.3359%"
],
[
"Samsung Bluetooth Earphone",
"84791",
"1.9307%"
],
[
"Essence Lotions",
"18550",
"0.4224%"
],
[
"Flour 2",
"12157",
"0.2768%"
],
[
"Oil 1",
"17379",
"0.3957%"
],
[
"Face Mask",
"11484",
"0.2615%"
],
[
"LG Smart TV",
"67865",
"1.5453%"
],
[
"Samsung Galaxy 2",
"127246",
"2.8974%"
],
[
"Bedroom Mirror",
"23857",
"0.5432%"
],
[
"Fireplace",
"73942",
"1.6836%"
],
[
"iPad 2",
"108816",
"2.4777%"
],
[
"Chips 1",
"7673",
"0.1747%"
],
[
"Panasonic Smart TV",
"129750",
"2.9544%"
],
[
"Outdoor Lighting",
"11568",
"0.2634%"
],
[
"Bathroom Slippers",
"7764",
"0.1768%"
],
[
"Samsung Tablet 1",
"50690",
"1.1542%"
],
[
"Shower curtains",
"8347",
"0.1901%"
],
[
"Towels",
"4363",
"0.0993%"
],
[
"Playstation 3",
"32682",
"0.7442%"
],
[
"Sony Headphone 2",
"35732",
"0.8136%"
],
[
"Samsung Galaxy 1",
"45974",
"1.0468%"
],
[
"Smart Lighting",
"14635",
"0.3332%"
]
],
"metadata": {
"aql": "explore {\n dimensions {\n products.name\n }\n measures {\n order_items.total_sales,\n order_items.percent_of_total\n }\n}",
"executed_sql": "WITH \"aql__t3\" AS (\n SELECT\n (\"order_items\".\"quantity\" * \"products\".\"price\") AS \"amount\",\n \"order_items\".\"product_id\" AS \"product_id\"\n FROM\n \"demo\".\"order_items\" \"order_items\"\n LEFT JOIN \"demo\".\"products\" \"products\" ON \"order_items\".\"product_id\" = \"products\".\"id\"\n), \"aql__t1\" AS (\n SELECT\n \"products\".\"name\" AS \"products->name\",\n SUM(\"order_items\".\"amount\") AS \"sum_order_items->amount\"\n FROM\n \"aql__t3\" \"order_items\"\n LEFT JOIN \"demo\".\"products\" \"products\" ON \"order_items\".\"product_id\" = \"products\".\"id\"\n GROUP BY\n 1\n), \"aql__t4\" AS (\n SELECT\n SUM(\"order_items\".\"amount\") AS \"sum_order_items->amount\"\n FROM\n \"aql__t3\" \"order_items\"\n)\nSELECT\n \"aql__t1\".\"products->name\" AS \"products->name\",\n \"aql__t1\".\"sum_order_items->amount\" AS \"order_items->total_sales\",\n ((\"aql__t1\".\"sum_order_items->amount\" * 1.0) / \"aql__t4\".\"sum_order_items->amount\") AS \"order_items->percent_of_total\"\nFROM\n \"aql__t1\"\n CROSS JOIN \"aql__t4\"\n",
"url": "https://play.amql.org/#code/uDritRdcsbtfekV2DrisS2OsH2FriisE2HtiVcRkZ2YtjYZgjsGxkDxYDtCxULrgtUVdfPTfl23tiZVjxKD2RtPxUGtZ2kxRFxbEuBxZF3ptMe3utkuBtgVsSrgntdRe2Wtk2o3LtVrnxXEtWifd2Xse20tcU3ktWxrG2B3M23tcxuDyRGxpFxrDxlExVEyfLxbFuFyjFtfyKbzvIyNRyFKxDD2C0tStg4mtUlTk5btZU0qGtfi2Hti2Otk2M2UxdHxcD0t7yULzlPyiK0wmzyIyKPyCK0tXzIF0rWxMH0p90pZygI0nmzsG0lwtlj0kR2PxVE0e9zTWyaH0YmzfF0XwtTZ2QtV0YK0mGxVD5D0X9zSUyZH0WmzeG0XarisErisHtmynD0WDsGtRdcrisSritT0gDtkrgsZlktPGsdtCDshnmrgsV2ysKGIrnxrEtU6DtR4ZtkjsHxpDxQNxJHyHHscxOG1nGtV3Vtdd3mtTVziGxqEtPjflxRDtPe2v1gExnEzYDxeE0BFtj2UtLz4G0IGsExNH21tekixQKtfi2sti6Wtk3BxSNxNItgi3PtlT4ixPHtl4X2bxLEtNytGti3r4Q1DDtjYZgy0KxWMsA0IFy5JxKD2RtPxUGzdFxbHuBxZFsErg2KtlVsBywHyKSzUI42tUyHG0HLxdIxcDyKg0rGyIVxMHyGh1VFyFO2PxVEx8j1FHyAIyOGxVD6Qx8Jx7EtN2FuFrisErisH2j2ntcjsHrisSrixOLz6LsGxTFsGtRdcxjDslxMErgxeLrguDyPE3ftgVsSrgntkRScVrnxRE4atS2oxSDsnxqKxYF2OtjTi4vz3ExeDxTG5ItRPjfliTVPe3pyLE0BExcFtfne3CxRHsYtYfcZj26tT4Y3BxeFyyFx0ItMrixlE2GsG2DzlL2NxqDypFtZ2m6wy6Drgtgi5NtlTk1ZD0FGzzKtAxaGrgshtUyiF0gJteld2htixUHtY243h0GDtWR6ztVxTG0eTxVEtWZe4KxUFsYtjhc3wuDrgjtDsntFCsbdGzJLuFFsTTxxEuFztQ0OHzrRsnxYEzp9zplzFJznZthlRe284GznQtBxYHzn9znizFJznZtRdfl2jzlQsZxWFzjby4NtRy4D1XHtkVdjy5KsCrgtgifUlTk2U2JtZTVrgzLN4JtRjli2Vtkfk5stPj2E5nzOQtExbErgtDxbEzTs3atdsAzXM0uGsBzFWtPzGLtPfWP2I3EzNa5ytWrgsZ2i1n401MuEyPDyxEzxMzqX3ptiTV5LzlEyFFznPtMritAxhGzkEzzF2Sznr14NzoLsCJGI4XsH0KN16Uz5LuFrisErisHtdf2wtcjsHtTR2ytX28tZ2psGxSFsGtRdcrisSrislxMErgxdK0VGzvH3ftS3czuF0kHrnsbxpJxXF28tjT2Ntg0HGrnxTG2itR4Xtf606OtPe3nyKE2m31xcFtfne5ExRHsYtYfcZj26tT4W3BxeFyxFx0ItMrixlE2GsG2DzjK2MxpDyoFtZ2ltejy5DrgtZUz4GzpKshtUyRF0OJteld2ZtixUHtY2w3bzyDtW62tjVxTG0NTxVEtWZeZxUGsYtjh6uuDDrgjtDsntFCsbd4132uFFsTTxpEuFzVQ0HEzXQsmxUDzZO5ZuAtkzX9zXOy4FzZZtgRi2KtkP11StAxZFrg189zlkzHKzqNuFrisErisHtdf263dsHtTx7D3asGxOFsG5XtcrisSrislxMErgxZG0DGzmHtk5ItcVy6F0SIsbxlFxTFzjTtUR2ytPjfliTVP12ExYD2m3txcFtfne50xRHsYtYfcZj26tT4O3BxeFytFx0ItMrixlE2GsG2DzbG2IxlDykFtZ2htejy1Drg0sDzwFzhKshtU1tH0GHteld2ZtixUHtY2w3XzuDtWR6PtVxTG0JTxVEtWZey3DxUEsYtjh6iuDDrgjtDsntFCsbd4x32uFFsTTxpEuFzVQ0DEzXQsmxUDzZOtkVuAtkzX9zXOy4FzZZtTflekiuBtP2I3HzhQsbxcGrg2I2czp9zpgzLNzxNuFrisErisHtdxXD3gsHxmGtZ3gsGxRFsG5htcrisSrislxME0QHxcD0NGztI5Stc0DG0cOxoDxWFzvTtUR21tPj2mtiTVPe3lyJE2m3zxcFtfneVixRHsYtYfcZj26tT4U3BxeFywFx0ItMrixlE2GsG2DzhJ2LxoDynFtZ2ktejy4D0AD06Dz4FzpM2UyUF0QKtVuAtkxSHtYZU2ezzDtWR6dtVxTG0OTxVEtWZeZxUGsYtjh6tuDDrgjtDsntFCsbdGy4FuFFsTTxrEuFzZQ0KEzZQsmxUDzZ9zZgy4FzZZ43te23te2NtkPzjU68xeHrgzt9ztkzPPz3NuFrisErisHtdf3C3isHtfi2HtijsGxOFsG2qtcrisSrislxMErgxZG0LGzpI5Qtcz8G0aIsnxlFxTFzoTtUR2ytPjfliTVzMFxYD2m3txcFtfn4jtixRHsYtYfcZj26tT4O3BxeFytFx0ItMrixlE2GsG2DzbG2IxlDykFtZ2htejy1DrgtZUzwGzhKshtU1yH0GHteld2ZtixUHtY2w3MzuDtWR6PtVxTG0JTxVEtWZeZxUGsYtjh6iuDDrgjtDsntFCsbd4x32uFFsTTxpEuFzVQ5W6HtljzZQtDxWFzdOtkVuAtkzb9zbOy8HzfZ3RtV2htVUP2FzjQsbxaGrgsZzVIznHtUxWD3v3Dzr9zrOzLLzvZ5OtViP3vzsQtFxXDrgshtUzpOteld2etizn9znOzDIzkZ2K3Vtfle5JzlPscxYHzm9zmizFJzna4VtZm33uBtPRkkVdgjzvRxgHrgsZxgGz39z3jzVQ0CNuFrisErisHtdfxlDtjsHtgi2ItlTkjsGxQFsGtRdcrisSrislxMErgxbI0VGzxHtk5atcVzIF0kItAxnHxVFzwTtU4KtRPjfliTVPe3jyIE2m3xxcFtfne4sxRHsYtYf47tj26tT4S3BxeFyvFx0ItMrixlE2GsG2DzfI2KxnDymFtZ2jtejy3DrgtZUz0GzlKshtU18H0KHteld2ZtixUHtY2w3ZzwDtWR6XtVxTG0LTxVEtWZeZxUGsYtjh6ouDDrgjtDsntFCsbd4z32uFFsTTxpEuFzVQtT6ItVXfiuBtPzeSsbxbHrgzn9znkzLMzwZteR2KzpQsmxUDziO3MuAtkzg9zgOy4FzZZ2ItiTYRek0BEzgPslxbHrgshtUznOteld2itizp9zpOzLMzwZtg3WtT5SzqPtAxVEzk9zkiy8GzeZ3QtVRkVUP2FzjQsbxaGrgsZtkzoOtUxWD3u3Dzq9zqOzLLzvNuFrisErisHtdf273esHtl3htijsGxNFsGtRdcrisSrislxMErgxYF0FGznHtk5KtczkG0UItFxkExSFzhT0ZDtRPjfli6ztPe3d0sFtV3rxcFtfne26xRHsYtYfcZj26tT4M3BxeFysFx0ItMrixlE2GsG2DzZF2HxkDyjF14Dtejy0Drg62zuGzfKshtU1qH0EHteld2ZtixUHtY2w3WztDtWR6LtVxTG0ITxVEtWZeZxUGsYtjh6fuDDrgjtDsntFCsbd4w32uFFsTTxpEuFzVQ2FtXePlgP1NDtVzfQtDxcDrgtFgrgscxcDzpOxuEzn9znOzLNzxh2lzvYsZtkztS313Dzx9zxWzLDzvZ3BtijkPeR2QzvQsexaErgxaEzvO4tuA0EIzr9zrHzHLzrZ4ZzqXskxZDzp9zplzFKzoZtVdRZx0DzkOsdxVEzg9zggy6GzcZtSZikYPUR35zhQsaxaE2qxaDzmOxSLzm9zmHzHLzrZtX2I3GtiznQsfxWFzjO3JuAtkzj9zjOy8HzfZtT24uBtP3rzgQsbxXDrg2XzhOteld2ez1Izj9zjHzDIzkZtWlccPeR2PzmQsexZDrgxZEzoOtkVuAtkzm9zmOzFKzoNuFrisErisHtdf263d2H6dtTYRekjsGxRFsG2ttcrisSrislxMErgxcJ0HGzqI5Mtc0AG0WIslxoIxWFzsTtUR21tPjfl2ntVzVFxYD2m3zxcFtfne3CxRHsYtYfcZj26tT4U3BxeFywFx0ItMrixlE2GsG2DzhJ2LxoDynFtZ2Utejy4DrgtZUz2GznKshtU15H0MHteld2ZtixUHtY2w3azxDtWR6btVxTG0MTxVEtWZeZxUGsYtjh6ruDDrgjtDsntFCsbd4032uFFsTTxpEuFzVQ0GEzXQsmxUDzZOtkVuAtkzX9zXOy4FzZZtT22uBtP1zSsbxXDrg149zhkzDIzkNuFriuFsEritR2psb2y6W2DrisSrisHHrg6ftgcfi5RztDxOD0KLtjxTJ2Btl4PtijsGtX2Z33tisExWJ23xPD2XtjkRk2hxWHysDxIF3ItRjl3ayIMtTfl34sAx1H4isBxyNznF2CzULzRQyGEtgifUlTk3CteR2b0hHyvOzeJ6K5ttd2ptkf4ktcPj2E2f0CDxeQtg2KtT1WDtPfWPxpFzJEyYF6362zSHsc5n3itVksmy1DrisSri28tdfPV5Utd2Ox7DxmDtT4A2k3GsetZ3ltA2qtYxjDsH2i2mtcjsHysMxTFsG3GtcriuF"
}
}
**4. Percent of total in one line**
The entire steps described above can also be straightforwardly condensed into one single-line expression.
```aml
Model order_items {
...
measure percent_of_total {
label: "Percent of Total"
type: 'number'
definition: @aql (sum(order_items.amount) * 1.0) / (sum(order_items.amount) | of_all(order_items));;
}
}
```