Here comes the sum(): it will, again, return the sum of new users by country and date. In this case, it’s the same SELECT statement as in the subquery from the previous example. After the keyword AS comes the CTE definition. The query below will give you the same result as the subquery solution:Įvery CTE is introduced using the WITH keyword. A more detailed explanation of the CTEs can be found in the article What Is a CTE?. The difference is that the CTE result exists only when a CTE is run together with the query using the CTE. You can use the result of a CTE as you would any other table. It is an expression that allows you to create a temporary result, which you can reference in another SELECT statement. A CTE is a tidier and “closer to the mathematical logic” version of a subquery. The other option for combining aggregate functions in SQL is using a CTE instead of a subquery. I want this average to be by country that’s why I grouped the data by the country column. Once I got the daily sum, I referenced this in the outer query by calculating the average of the column ds.sum_new_users – i.e. ![]() I gave the subquery the alias ds, which is short for ‘daily sum’. This returns the total daily number of new users. The above code selects the date and country and calculates the sum of the column number_of_new_users. ![]() Then the result is used in the aggregate functions of the outer query. The principle when combining two aggregate functions is to use the subquery for calculating the ‘inner’ statistic. SUM(number_of_new_users) AS sum_new_users In this example, I’ll put the subquery in the FROM clause.ĪVG(ds.sum_new_users) AS average_daily_new_users When creating reports, they are usually found in the SELECT, FROM, or WHERE clauses. The subquery is a query within the main query. The first option is to combine two aggregate functions using a subquery. There are two options: using a subquery or using Common Table Expressions (CTEs). How do you fix this query to combine two aggregate functions in SQL? Well, you can, but the query will return an error saying exactly what I just said: In other words, you can’t use an aggregate function within an aggregate function. Why not, right? There’s a very simple reason why not: SQL does not allow the nesting of aggregate functions. Transferred to SQL functions, it seems logical to write something like this:ĪVG(SUM(number_of_new_users)) AS average_new_daily_users If you only think about the mathematical approach, it’s simple: you first have to sum the number of new users per country every day, then calculate the average of that sum. It’s a shame that this naive solution is no solution at all. The logic behind the naive approach is, actually, quite sound. How would you use the AVG() and SUM() functions to calculate the average daily number of new users by country? The Naive Solution You see below there are two dates in total, with every date having a different number of users from two countries and two cities in each country. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |