Postgresql SUM calculated column

Question

I am trying to create some sql to calculate the worth of a users inventory and have manage to get it to work up to the final step.

SELECT DISTINCT ON (pricing_cards.card_id)
    (inventory_cards.nonfoil * pricing_cards.nonfoil) + (inventory_cards.foil * pricing_cards.foil) as x
FROM inventory_cards 
    INNER JOIN pricing_cards ON pricing_cards.card_id = inventory_cards.card_id
WHERE inventory_cards.user_id = 1
ORDER BY pricing_cards.card_id, pricing_cards.date DESC;

The code above bring back a single column that has the correct calculation for card. I now need to sum this column together but keep getting errors when I try to sum it.

Adding SUM((inventory_cards.nonfoil * pricing_cards.nonfoil) + (inventory_cards.foil * pricing_cards.foil)) throws the following error

ERROR:  column "pricing_cards.card_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 6: ORDER BY pricing_cards.card_id, pricing_cards.date DESC;

Adding GROUP BY pricing_cards.card_id, pricing_cards.date seems to fix the errors but is returning the same column of calculated values.

so:

SELECT DISTINCT ON (pricing_cards.card_id)
    SUM((inventory_cards.nonfoil * pricing_cards.nonfoil) + (inventory_cards.foil * pricing_cards.foil)) as x
FROM inventory_cards 
    INNER JOIN pricing_cards ON pricing_cards.card_id = inventory_cards.card_id
WHERE inventory_cards.user_id = 1
GROUP BY pricing_cards.card_id, pricing_cards.date
ORDER BY pricing_cards.card_id, pricing_cards.date DESC;

Returns:

x
0.71
29.92
25.67
171.20
0.32
0.26

Do you really need the `DISTINCT ON (pricing_cards.card_id)`? I doubt you have multiple rows with the same user_id and card_id in those tables, do you? So just omit that, as well as the superfluous `ORDER BY` clause. — Bergi, Nov 14 '21 at 19:53
The data in the `pricing` table will as it had `id`, `card_id`, `date`, `nonfoil`, `foil`. The last two column being the pricing for a card on a specific date. This table will hold 30 days worth of pricing data for each card. Therefor I am trying to get the latest pricing data for each card. The `user_id` is on the `inventory` table — Ross, Nov 14 '21 at 20:18

score 0 · Answer 1 · answered Nov 14 '21 at 20:43

I suggest you use a subquery to get the latest pricing data, then join and sum:

SELECT
  SUM(inventory_cards.nonfoil * latest_pricing.nonfoil + inventory_cards.foil * latest_pricing.foil)
FROM inventory_cards 
INNER JOIN (
  SELECT DISTINCT ON (card_id)
    card_id, nonfoil, foild
  FROM pricing_cards
  ORDER BY pricing_cards.card_id, pricing_cards.date DESC
) AS latest_pricing USING (card_id)
WHERE inventory_cards.user_id = 1

For alternatives in the subquery, see also Select first row in each GROUP BY group? and Optimize GROUP BY query to retrieve latest row per user.

Postgresql SUM calculated column

1 Answers1