You can divide by the unit and add the average order size:
SELECT avg("price USD" / grams) AS average_price_per_gram,
avg(grams) AS average_order_size
This statement will return a single number: the average over all values specified in te WHERE condition.
If you want several result rows, grouped by month, add an appropriate GROUP BY clause:
Here is what you should do - obviously it depends on precisely what you mean by "price" and "average" - I think that I've covered all the bases (see the fiddle here):
CREATE TABLE blueberry_order -- a table is a set which is always singular
-- except where this would be a keyword i.e. "order"
GROUP BY aggregates the result values of the SELECT statement based on its arguments.
Removing the GROUP BY clause and arguments from your SQL will allow duplicates to be presented.
For MariaDB recommend sql_mode=ONLY_FULL_GROUP_BY (not default, ref: manual) to ensure that SQL statements are correctly formed with respect to GROUP BY.
That query will get faster if you increase work_mem (because then there will be no more "lossy" blocks).
The idea to first select all ids from one table and then select rows from another table based on these ids is fundamentally wrong. You should instead join the two tables and do the same work with a single query.
This it a variation of a greatest-n-per-group problem.
I would do this with a join to a derived table:
from application app
select distinct on (application_id) *
order by application_id, last_modified desc
) ast on ast.application_id = app.id;
I would first calculate the sum per month, then calculate the average from that:
select avg(amount) as avg_amount_per_month
SELECT date_trunc('month', "purchase time"), SUM("amount in EUR") as amount
FROM personal."food purchases"
WHERE "purchase time" >= now() - INTERVAL '3 months'
group by ...
SELECT SUM(PRE_VAR) AS IMPORTO
WHERE ID_VARIANTI IN (104 , 104, 142)
to, for example,
SELECT SUM(PRE_VAR) AS IMPORTO
JOIN (SELECT 104 ID_VARIANTI UNION ALL
SELECT 104 UNION ALL
SELECT 102) criteria USING (ID_VARIANTI)
or use any other technique for to create a rowset (for example, transfer criteria as ...
The IN predicate will either evaluate to TRUE or FALSE (or NULL) exactly once for each row, so you won't get any multiplier effect from that. You can use a Common Table Expression (CTE, only supported in 8+) or a temporary table
CREATE TABLE vo_plurep
( ID_PLUREP int not null
, PRE_PRP int not null
insert into vo_plurep (id_plurep, pre_prp) values (1,1),(...
These users form a graph. They are linked by edges to common attributes (email, device etc.). The question boils down to finding disjoint subgraphs within the data, and assigning each a label.
There's an answer on SO which addresses this. The web will have many more if you care to search.
SQL Server 2019 introduced SHORTEST_PATH() which would simplify the ...
For your query with ...
a very small LIMIT 40
and not very selective WHERE conditions ①
... this partial, multicolumn expression index might work wonders:
CREATE INDEX foo ON activities (COALESCE(origin_created_at, created_at) DESC, id DESC)
WHERE ("isBulk" = false OR type = 0) AND deprecated_at IS NULL;
① Currently, after doing a lot more work ...
My understanding is that you want average price per month from last three months. For example if you spent 45 USD in last three months your average price per month should be 15 USD (45/3). In that case you can use the following query:
SELECT sum("price USD")/3
FROM "blueberry orders"
WHERE "timestamp" >= now() - INTERVAL '3 ...
As Mustaccio mentioned in the comments, you want rows where dev_type is either 'x1' or 'x2', not where it's 'x1' and 'x2'.
Also you only need the brackets around each of the alternative sets of conditions. The others aren't doing anything and they are just confusing.
with test_table (customer_num, dep_group, dep_type, balance) as
( select 1, 'x', 'x1', ...