Use number of days represented by query

1

I feel stupid asking this question because it seems quite basic, but I can’t figure it out.

I’m using InfluxDB and Grafana to dashboard energy usage based on different tariffs. The cost for the basic tariff is computed by multiplying the consumption (in kWh) in a time period (and single DB row) by a constant representing the cost-per-kWh. So I can print the cost for the period selected in Grafana just by doing:

SELECT sum("consumption") * 0.15 FROM "electricity" WHERE $timeFilter

Each tariff also includes a daily standing charge, also a constant. I want to add this standing charge to the figure computed above for display in the dashboard panel. The problem is that I need the number of days represented by the query so that I can multiply this by the constant. How do I get at that number of days? I know that I have the __from and __to variables in Grafana which represent the range of the query embedded in $timeFilter, but how do I use these to augment my query? I also know that I can use a subquery to count the number of days, something like:

select count(*) from (select mean(consumption) from electricity group by time(1d));

but it’s not clear that I can use this result in my Grafana query to give me the final number I want (and also it seems kind of clumsy).

Can anybody help?

2

Hello @Peter_Saffrey,
Don’t feel stupid! It’s not basic if you’ve never done it before ;p
I’m I’m understanding you correctly, I think you. want something like this?

SELECT sum("consumption") * 0.15 * "mycount" FROM (SELECT count(*) as "mycount" FROM "electricity" group by time(1d));

Yes I agree, nested subqueries are clumsy. That’s why Flux was created :stuck_out_tongue: