sum() in postgresql is returning individual rather than aggregated values

user_13783722 0
Viewed 102

I'm having trouble getting a sum() function working in my database. I am querying table purchases for the store_id (retrieved from table stores), month(+year) of purchase (stored as a timestamp without time zone specified), and the sum of the total sales income by month. The problem is that no aggregation occurs, so that each sale's total is being reported as the monthly total of sales for a given store. I suppose I'm not using `sum()' correctly, but I haven't found a solution. Suggestions and advice appreciated.

SELECT --month of purchase, total sales
    purchases.store_id AS store_id, 
    TO_CHAR(timestamp, 'Month'||'YYYY') AS month,
    sum(total) AS total_sales
    FROM purchases
    GROUP BY store_id, timestamp, purchases.total
    ORDER BY month, store_id;
sum datetime postgresql sql database
1 Answers

You are grouping by timestamp while you want to group by month.

Consider:

SELECT
    store_id,
    date_trunc('month', timestamp) AS month,
    sum(total) AS total_sales
FROM purchases
GROUP BY store_id, date_trunc('month', timestamp)
ORDER BY month, store_id;

This gives you month as a date truncated to the first day of the month: I find this more meaningful that a formatted string, but you can change that back to the original to_char() expression if you prefer.

Note that PostgreSQL also supports positional arguments in the GROUP BY and ORDER BY clause, which lets you write:

SELECT
    store_id,
    date_trunc('month', timestamp) AS month,
    sum(total) AS total_sales
FROM purchases
GROUP BY 1, 2
ORDER BY 2, 1;
user_106767160
Related