1

I am using the Nothwind data base & working in pgAdmin, and my query is looking like this at the moment

SELECT 
    TO_CHAR (o.ShippedDate, 'yyyy.MM') AS Month 
    ,o.OrderID
    ,Total
    ,SUM (Total) OVER PARTITION BY TO_CHAR (ShippedDate, 
    ‘yyyy.MM’) ORDER BY O.OrderID) AS Running_Total 
FROM public.orders O
INNER JOIN (
  SELECT OrderID, SUM(Quantity*UnitPrice) AS Total 
  FROM public.order_details 
  GROUP BY OrderID 
  ORDER BY OrderID
) OD ON O.OrderID = OD.OrderID
WHERE
    TO_CHAR (o.ShippedDate, 'yyyy.MM') IS NOT NULL

And is is not working, it says:

ERROR: column "o.shippeddate" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: TO_CHAR (o.ShippedDate, 'yyyy.MM') AS Month

Can you help me out what could be the issue? Thanks!

I fixed the query, so it is now the correct one.

Timea Dbs
  • 11
  • 2
  • Your issue isn't related to the join, it's how you're using SUM(). Check the answers here https://stackoverflow.com/questions/22841206/calculating-cumulative-sum-in-postgresql for more information. – Eric McKeeth Oct 19 '21 at 18:11
  • omg yes! Thank you, I just fixed it - it is working now. – Timea Dbs Oct 19 '21 at 18:22
  • Does this answer your question? [Calculating Cumulative Sum in PostgreSQL](https://stackoverflow.com/questions/22841206/calculating-cumulative-sum-in-postgresql) – Pooya Oct 19 '21 at 20:08

0 Answers0