Hot answers tagged

4

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: GROUP ...


4

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" ( ...


3

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.


2

SELECT Job_Id, MAX(Monitor_Date) Monitor_Date FROM source_table GROUP BY Job_Id


2

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.


1

I would use DISTINCT ON: SELECT DISTINCT ON (application_id) * FROM application JOIN asset USING (application_id) ORDER BY application_id, asset.last_modified_by DESC;


1

This it a variation of a greatest-n-per-group problem. I would do this with a join to a derived table: select * from application app join ( select distinct on (application_id) * from asset order by application_id, last_modified desc ) ast on ast.application_id = app.id;


1

I would first calculate the sum per month, then calculate the average from that: select avg(amount) as avg_amount_per_month from ( 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 ...


1

Convert SELECT SUM(PRE_VAR) AS IMPORTO FROM vo_varianti WHERE ID_VARIANTI IN (104 , 104, 142) to, for example, SELECT SUM(PRE_VAR) AS IMPORTO FROM vo_varianti 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 ...


1

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),(...


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 ...


1

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 ...


1

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 ...


1

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', ...


Only top voted, non community-wiki answers of a minimum length are eligible