-2

I semi-regularly make orders of "blueberries". Usually, I order 10 grams of it, but sometimes 5. I log these in this PostgreSQL table:

CREATE TABLE "blueberry orders"
(
    id serial,
    "timestamp" timestamp with time zone NOT NULL DEFAULT now(),
    grams integer,
    "price USD" numeric,
    PRIMARY KEY (id)
)

I have this query:

SELECT avg("price USD") FROM "blueberry orders" WHERE "timestamp" >= now() - INTERVAL '3 months';

However, I have a feeling that at least two things are wrong with this:

  1. It doesn't account for the fact that some orders are of 5 grams and not 10 grams, messing up their values.
  2. Does this really give me the correct "average amount per month"? Doesn't it just give the average amount PER ORDER, which is not what I'm looking for?

I realize that I probably will need to do a calculation with the "grams" column somehow, but the second point worries me even more: I don't think I'm pulling out the right numbers at all?

To make it very clear, I'm looking for a number such as "X USD is what I typically spend in total per month on blueberries, based on the last quarter's purchases". The orders can sometimes occur twice a month, or once every 1.5 months, or once every 2 months, or weekly, and could also be 3 grams or 15 grams potentially. (It shouldn't assume just 5/10 gram orders.)

I've really spent a lot of time thinking about this, but I think I'm lost. Maybe I'm making it more complicated than it needs to be...

  • Is "price USD" the extensive price (the cost of the order) or the intensive price (the price per gram, at the time of that order)? – jjanes Jun 15 at 12:59
  • 2
    I'd help if you add some sample data using a fiddle and the desired result. Please have a look at: How to create a Minimal, Complete, and Verifiable Example for database-related questions – McNets Jun 15 at 20:54
  • You can use mixed case identifiers in PostgreSQL just fine. It'll fold them to lower case for you. Note: case-folding is in the SQL spec. That PostgreSQL lets you have case-sensitive identifiers is an vioation/extension of the SQL spec. Just don't use quotes. CREATE TABLE Foo ( MixedCase int ); INSERT INTO foo ( mixedcase ) VALUES ( 1); INSERT INTO foo ( MixedCase ) VALUES ( 1); – Evan Carroll Jun 15 at 22:11
  • Do you store the price per gram or the order price in that price column? – ypercubeᵀᴹ Jun 15 at 22:13
  • 1
    I’m voting to close this question because it's been abandoned by the author. – mustaccio Jun 17 at 15:57
4
  1. 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
    
  2. 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 BY date_trunc('month', "timestamp")
    
| improve this answer | |
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"
(
    bo_id SERIAL NOT NULL,  -- should now use GENERATED ALWAYS AS IDENTITY
    bo_timestamp TIMESTAMP NOT NULL DEFAULT NOW(),  -- with time zone messes up the generated field, you could use a trigger?
    weight_g INTEGER CHECK (weight_g > 0),
    price_usd NUMERIC NOT NULL CHECK (price_usd > 0),
    purchase_month INTEGER GENERATED ALWAYS AS (EXTRACT (MONTH FROM bo_timestamp)::INT) STORED,
    -- can't use VIRTUAL
    CONSTRAINT blueberry_orders_pk PRIMARY KEY (bo_id)
);

Populate it with made up sample data:

--
-- purchase time is the same for all orders - it's
-- not relevant to the question at hand!
--
INSERT INTO blueberry_order (bo_timestamp, weight_g, price_usd) VALUES
('2020-06-05 10:15:54', 10, 100),
('2020-06-03 10:15:54', 15,  75),
('2020-05-03 10:15:54',  5,  45),
('2020-05-09 10:15:54',  7,  70),
('2020-05-15 10:15:54', 20, 150),
('2020-05-25 10:15:54',  5,  30),
('2020-04-05 10:15:54', 25, 200),
('2020-04-05 10:15:54', 15, 125),
('2020-04-05 10:15:54',  5,  25), -- discount for previous bulk purchases!
('2020-04-05 10:15:54', 10,  80),
('2020-03-05 10:15:54', 20, 100),
('2020-03-05 10:15:54', 15, 150),
('2020-03-05 10:15:54', 25, 175);

Then, run four queries with different definitions for price and average:

Query 1:

-- price per gramme MARK I - how much you paid per gramme per month
-- if the price was for the sale IN TOTAL!
--
SELECT 
  TO_CHAR(TO_DATE (bo.purchase_month::TEXT, 'MM'), 'Month') AS m_name, 
  ROUND(SUM(price_usd::NUMERIC)/(SUM(bo.weight_g::NUMERIC)), 2)
FROM blueberry_order bo
GROUP BY bo.purchase_month
ORDER BY m_name DESC;

Result:

m_name  round
May         7.97
March       7.08
June        7.00
April       7.82

Query 2:

-- 
-- price per gramme MARK II - how much was paid per gramme per month 
-- IF the price was PER GRAMME!
--

SELECT 
  TO_CHAR(TO_DATE (bo.purchase_month::TEXT, 'MM'), 'Month') AS m_name,
  ROUND(SUM(bo.weight_g * bo.price_usd)::NUMERIC/(SUM(bo.weight_g::NUMERIC)), 2)
FROM blueberry_order bo
GROUP BY bo.purchase_month
ORDER BY bo.purchase_month DESC;

Result:

m_name      round
June        85.00
May         104.46
April       141.82
March       143.75

Query 3:

--
-- total payments - How much was paid per month IF 
-- the price was PER GRAMME!
--

SELECT 
  TO_CHAR(TO_DATE (bo.purchase_month::TEXT, 'MM'), 'Month') AS m_name,
  SUM(bo.weight_g * bo.price_usd)
FROM blueberry_order bo
GROUP BY bo.purchase_month
ORDER BY bo.purchase_month DESC;

Result:

m_name       sum
June        2125
May         3865
April       7800
March       8625

Query 4:

--
-- Average paid per order per month IF the
-- price is for the sale IN TOTAL

SELECT 
  TO_CHAR(TO_DATE (bo.purchase_month::TEXT, 'MM'), 'Month') AS m_name,
  ROUND(SUM(bo.price_usd)::NUMERIC/
    COUNT((EXTRACT(MONTH FROM bo.bo_timestamp))::INT), 2) AS avg
FROM blueberry_order bo
GROUP BY bo.purchase_month, EXTRACT(MONTH FROM bo.bo_timestamp)
ORDER BY EXTRACT(MONTH FROM bo.bo_timestamp) DESC;

Result:

m_name  avg
June        87.50
May         73.75
April       107.50
March       141.67

I'll leave Query 5 (the average per order per month if the price is the price PER GRAMME) it should be easy enough with all you have above!

Re. WITH TIME ZONE - if this is really important to you, then you can use a trigger to update the purchase_date field instead of the GENERATED ALWAYS AS (EXTRACT (MONTH FROM bo_timestamp)::INT) technique.

| improve this answer | |
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 months';
| improve this answer | |
  • If the "price USD" is for single gram then following query can be used: SELECT sum("price USD" * grams)/3 FROM "blueberry orders" WHERE "timestamp" >= now() - INTERVAL '3 months'; – rks2020 Jun 17 at 13:33
  • @G. Zwickey: When asking for average spendings per month, actual spendings are all you need to consider. This is the answer. – Erwin Brandstetter Jun 17 at 15:19
0

It seems you want a weighted average.

If the price column stores price per gram:

SUM(price * grams) / SUM(grams) 

If the price column stores price of order:

SUM(price) / SUM(grams) 
  • 1
    If I understood the explanation correctly, it should probably be SUM(price) / SUM(grams), because price is the amount paid for the order, not an amount per gram. I'm not 100% sure, though – Andriy M Jun 15 at 22:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.