Title
s

Super Richman

03/10/2023, 8:21 AM
why does this causes error? 😞
``SELECT (SUM(AVG(visMiles))) FROM weather``
b

Bolek Ziobrowski

03/10/2023, 8:28 AM
Short answer is - because it makes no sense 🙂 Avg would produce a single row so what would you like to sum it with ?
Aggregate function calls can't be nested .
s

Super Richman

03/10/2023, 8:37 AM
hmmm my usecase is something like that SELECT (SUM(cloudCeiling + AVG(visMiles))) FROM weather
m

Miguel Arregui

03/10/2023, 8:46 AM
I also have a cool case, I will show you how I solve it. The case I have a table tax:
``````CREATE TABLE tax (
ts TIMESTAMP,
concept SYMBOL,
amount FLOAT
) TIMESTAMP(ts) PARTITION BY YEAR;``````
with an unknown granularity, the taxman issues a charge to my bank account, and these end up in the tax table. With this query I can know how much I have paid for two different concepts for a given year:
``````SELECT ss.ts, irpf.sum irpf, ss.sum ss, irpf.sum + ss.sum total
FROM
(SELECT ts, concept, sum(amount) FROM tax
WHERE concept='Pago de impuestos' AND year(ts) = 2022
SAMPLE BY 3M ALIGN TO CALENDAR) irpf
ASOF JOIN
(SELECT ts, concept, sum(amount) FROM tax
WHERE concept='Cargo cuota SS' AND year(ts) = 2022
SAMPLE BY 3M ALIGN TO CALENDAR) ss``````
and if I want to total for the year:
``````WITH taxes_by_quarter AS (
SELECT ss.ts, irpf.sum irpf, ss.sum ss, irpf.sum + ss.sum total
FROM
(SELECT ts, concept, sum(amount) FROM tax
WHERE concept='Pago de impuestos' AND year(ts) = 2022
SAMPLE BY 3M ALIGN TO CALENDAR) irpf
ASOF JOIN
(SELECT ts, concept, sum(amount) FROM tax
WHERE concept='Cargo cuota SS' AND year(ts) = 2022
SAMPLE BY 3M ALIGN TO CALENDAR) ss)
SELECT sum(irpf), sum(ss), sum(total) FROM taxes_by_quarter;``````
s

Super Richman

03/10/2023, 9:11 AM
also this not working
``SELECT cloudCeiling + AVG(visMiles) FROM weather``
hmm I dont understand exactly how its related
@Bolek Ziobrowski my point is how can I use agg funcs in calculation with a normal column?
j

javier ramirez

03/10/2023, 9:26 AM
In a simple select statement you can’t. In standard SQL when you are not using GROUP BY you get an output row for each table row matching your conditions in the WHERE/JOIN. When you use GROUP BY, you get an output row for each different combination of the columns being part of the group. The columns that are not part of the group by need to be aggregates. If you try to use a non-aggregated column which is not part of the GROUP BY, you get an error, which is what you were trying to do in your initial statement. (more to follow)
In your case, if you want to have a single row for each different cloudCeiling value, in which you are summing that cloudCeiling value with the average VisMiles for all the rows with the same cloudCeiling value you could do this
``SELECT cloudCeiling, cloudCeiling + AVG(visMiles) FROM weather``
``order by cloudCeiling``
notice we are not doing a GROUP BY, as questdb implicitly will do a group by for every non aggregated column, but you could use GROUP BY and this would be standard SQL
``````SELECT cloudCeiling, cloudCeiling + AVG(visMiles) FROM weather
group by cloudCeiling order by cloudCeiling``````
Just to see it better, you can add the average as an extra column to this query, as in
``````SELECT cloudCeiling, AVG(visMiles) as local_average, cloudCeiling + AVG(visMiles) as total FROM weather
group by cloudCeiling order by cloudCeiling``````
image.png
As you can see, in this result the average in each row is a different one, as it is averaging only the visMiles for the rows matching the cloudCeiling in that row. Your output contains as many rows as unique values you have in the cloudCeiling column
If you want to add each cloudCeiling with the total average of the table, we need to do it in two steps
``````WITH total_average AS (
SELECT AVG(visMiles) as total_average FROM weather
)
SELECT cloudCeiling, total_average, cloudCeiling + total_average as total FROM weather
cross join total_average``````
image.png
as you can see in this case we are getting an output row for each individual row on the weather table, and all of the rows are going to be adding with the total average of the whole table, which remains the same for each row
hopefully this helped!
b

Bolek Ziobrowski

03/10/2023, 10:27 AM
@Super Richman "SELECT cloudCeiling + AVG(visMiles) FROM weather" hits a bug that is fixed in https://github.com/questdb/questdb/pull/3049 . Until it's merged you've to add cloudCeiling as an explicit group by key .
s

Super Richman

03/10/2023, 3:26 PM
Thanks all! you are amazing 🤗