Title
s
Super Richman
03/10/2023, 8:21 AMwhy does this causes error? 😞
SELECT (SUM(AVG(visMiles))) FROM weatherb
Bolek Ziobrowski
03/10/2023, 8:28 AMShort 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 AMhmmm my usecase is something like that
SELECT (SUM(cloudCeiling + AVG(visMiles))) FROM weather
m
Miguel Arregui
03/10/2023, 8:46 AMI 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) ssand 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 AMalso this not working
SELECT cloudCeiling + AVG(visMiles) FROM weatherhmm 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 AMIn 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 weatherorder by cloudCeilingnotice 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 cloudCeilingJust 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 cloudCeilingimage.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_averageimage.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 PMThanks all! you are amazing 🤗