In SQL Server the VAR() function returns the statistical variance of all values in the specified expression.
Author: Ian
Fix “Incorrect syntax near the keyword ‘DISTINCT'” Error in SQL Server
If you’re getting an error that reads “Incorrect syntax near the keyword ‘DISTINCT’” when using the DISTINCT clause in SQL Server, it could be that you’ve put the DISTINCT clause in the wrong position.
When using the DISTINCT clause, it must be the first item in the SELECT list.
Therefore, to fix this error, check the position of the DISTINCT keyword. If it’s not the first item in the SELECT list, move it to the front so that it is the first item in the SELECT list.
How the SQL UNION Operator Deals with NULL Values
The SQL UNION operator concatenates the results of two queries into a single result set. By default it returns distinct rows (i.e. it removes any redundant duplicate rows from the result set). But we can also use UNION ALL to return non-distinct rows (i.e. retain duplicates).
When it comes to NULL values, it’s pretty straight forward. SQL treats two NULL values as non distinct values. In other words, they’re duplicates.
Continue readingA Quick Overview of SQL Server’s STDEVP() Function
In SQL Server the STDEVP() function returns the statistical standard deviation for the population for all values in the specified expression.
How to Select the Date Column with the Latest Date in SQL Server
Suppose you have a query that returns multiple date columns, and suppose you want to return the latest date, regardless of which column it came from.
As from SQL Server 2022 we can use the GREATEST() function to easily achieve this outcome.
Understanding the STDEV() Function in SQL Server
In SQL Server the STDEV() function returns the statistical standard deviation of all values in a specified expression.
So we can use this function to get the standard deviation of all values in a column.
Continue readingFix Error “The function ‘RANK’ must have an OVER clause with ORDER BY” in SQL Server
When using window functions such as RANK() in SQL Server, we must provide an OVER clause clause with an ORDER BY clause.
If you’re getting an error that reads “The function ‘RANK’ must have an OVER clause with ORDER BY”, it’s probably because you’re including an OVER clause with the RANK() function (as is required), but you’re omitting the ORDER BY clause.
To fix this error, add an ORDER BY clause to the OVER clause.
An Introduction to SQL Server’s GROUPING_ID() Function
SQL Server has a GROUPING_ID() function that returns the level of grouping based on its arguments.
We pass one or more column expressions to the function, and it computes the level of grouping. The column expressions must match the ones provided by the GROUP BY clause.
A Quick Overview of UNION DISTINCT in SQL
If you’ve ever used the UNION clause in a SQL query, chances are you used UNION ALL or just UNION by itself. But some RDBMSs also accept a UNION DISTINCT option.
The UNION DISTINCT option is basically the equivalent of UNION by itself. That is, it removes redundant duplicate rows.
Fix Error “function lag(numeric, numeric) does not exist” in PostgreSQL
If you’re getting an error in PostgreSQL that reads something like “function lag(numeric, numeric) does not exist“, it could be because your second argument is the wrong data type.
The second argument to the lag() function is optional, but if it’s provided, it must be an integer.
So to fix this issue, make sure the second argument is an integer. Alternatively, you can omit the second argument altogether if you’re happy to use the default value of 1.