aggregation

Calculating Cumulative Offsets with DATEADD() and Window Aggregates in SQL Server

by

Window functions in SQL Server aren’t just about ranking and numbering rows. When you combine aggregate window functions with DATEADD(), you can create running totals that translate into meaningful date calculations. This approach is particularly valuable when you need to calculate delivery schedules, project timelines, or any scenario where accumulated values should push dates further into the future.

The pattern involves using SUM() or another aggregate with the OVER clause to create a running total, then feeding that total into DATEADD() to offset a base date. The result is a dynamic date that reflects the cumulative impact of your data. Let’s explore this with a simple example.

Read more

How to Prevent Overflow Errors When Aggregating Data in SQL Server

by

When working with aggregate functions in SQL Server, it’s easy to overlook that certain datatypes have strict limits. If you’re summing values in large tables (or even just summing very large numbers), you might run into an arithmetic overflow error. This happens when the result of an aggregate exceeds the maximum value the datatype can hold.

Understanding how this works and how to prevent errors will help you write reliable queries.

Read more

How to Use STRING_AGG() in SQL Server with Custom Separators and Sorting

by

In SQL Server, STRING_AGG() is an aggregate function that concatenates string values from a group into a single string. It’s a handy tool for doing things like creating comma-separated lists from related data.

In this article we’ll check out how to use STRING_AGG() with different separators. We’ll also see how we can control the order of the concatenated strings.

Read more

Fixing Error 10754: “The function ‘APPROX_PERCENTILE_DISC’ must have a WITHIN GROUP clause” in SQL Server

by

When using the APPROX_PERCENTILE_DISC() function in SQL Server, you may get an error that reads “The function ‘APPROX_PERCENTILE_DISC’ must have a WITHIN GROUP clause“. If you get this error, it’s because you omitted the WITHIN GROUP clause when using the APPROX_PERCENTILE_DISC() function.

To fix this issue, make sure you include the WITHIN GROUP clause whenever you use the APPROX_PERCENTILE_DISC() function.

Read more

Fix “The ORDER BY in WITHIN GROUP clause of ‘APPROX_PERCENTILE_CONT’ function must have exactly one expression” in SQL Server

by

If you’re getting SQL Server error 10751 that reads “The ORDER BY in WITHIN GROUP clause of ‘APPROX_PERCENTILE_CONT’ function must have exactly one expression” it’s probably because you’re using too many ORDER BY expressions with the APPROX_PERCENTILE_CONT() function.

The APPROX_PERCENTILE_CONT() function requires the WITHIN GROUP clause, and that clause requires an ORDER BY sub-clause. However, that ORDER BY sub-clause requires exactly one expression – no more, no less. So, you can’t pass multiple expressions, and you can’t pass zero expressions. It must be exactly one expression.

To fix this issue, be sure to have just one ORDER BY expression in the WITHIN GROUP clause when using the APPROX_PERCENTILE_CONT() function.

Read more

SQL Server ROLLUP() vs WITH ROLLUP

by

When using the GROUP BY clause in SQL Server, we can use the ROLLUP modifier to create subtotals and grand totals, etc.

You may have seen two variations of this. One as GROUP BY ROLLUP () and the other as GROUP BY ... WITH ROLLUP.

You may be wondering which one you should use?

As it turns out, Microsoft recommends that we use the first syntax; GROUP BY ROLLUP (). The other syntax is provided for backward compatibility only.

Read more

Fixing the “data types” are “incompatible in the approx_percentile_cont operator” in SQL Server

by

If you’re getting SQL Server error msg 402 that tells you the “data types” are “incompatible in the approx_percentile_cont operator“, it’s probably because you’re trying to apply the APPROX_PERCENTILE_CONT() function against a non-numeric column (or one that doesn’t evaluate to a numeric type).

When using the APPROX_PERCENTILE_CONT() function, the ORDER BY expression in the WITHIN GROUP clause must evaluate to an exact or approximate numeric type. Other data types are not allowed, and will result in the above error.

To fix this issue, be sure to apply the function against a numeric column/expression.

Read more

Fix SQL Server Error: “The function ‘APPROX_PERCENTILE_CONT’ must have a WITHIN GROUP clause”

by

If you’re getting SQL Server error 10754 that reads “The function ‘APPROX_PERCENTILE_CONT’ must have a WITHIN GROUP clause” it’s probably because you’re calling the APPROX_PERCENTILE_CONT() function, but you’ve omitted the WITHIN GROUP clause.

To fix this issue, add a WITHIN GROUP clause to the function (and make sure it has an ORDER BY clause).

Read more