SQL Aggregate Window Functions & Fixed LOD Expressions

SQL window functions are confusing – so are fixed level of detail (LOD) expressions in Tableau. Then, why am I comparing them? Wouldn’t that make everything even more complicated?!

When I was taught LODs in my data analytics bootcamp, they were compared to SQL window functions; that connection was game-changing and helped me understand fixed LOD expressions. I aim to illustrate that relationship in this blog post.

(If you would like more LOD content, there is plenty here on the Data School blog. For example, Hanna Nykowska wrote Intro to Tableau LODs (fixed) and my colleague, Bianca Ng, wrote LOL: LODs of Love.)

If you didn’t know, window functions:

  • Perform aggregation or ranking over a window of data (set of rows)
  • Can be recognized by the presence of the OVER() clause in the SELECT statement

Speaking of the OVER() clause, it can include the:

  • PARTITION BY clause - partitions rows by a stated measure
  • ORDER BY clause - orders rows within those partitions in a specified way

Today, I will focus on the SUM() window function in SQL, which returns the sum of the input column over a window of data.


In the window function above, I am calculating the SUM of Sales (partitioned) by Category. (Moreover, I named this column “Total Sales by Category” and rounded it to zero decimal places.)

The SQL query generates the following result:


The column names help in understanding the table, but once again, the window function in this instance computes the sum of sales by category. There are repeated values in that column because there are only three categories and these values are the same within each category.

What’s the equivalent of this in Tableau?

LODs!


In the LOD above, for every Category, I am calculating the SUM of Sales.

By adding this calculation to my view, I can generate a text table that is identical to the SQL query results.


Similar to the SUM() window function, the LOD expression computed the sum of sales by category. Also, the values in “Total Sales by Category” reappear because they are fixed by category.

SQL window functions and LOD expressions can be complex, but I hope that by comparing them in this way, your understanding of one, the other, or both has improved. They are thematically similar after all!

Author:
Elaine Yuan
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2025 The Information Lab