CODEWITHSUNDEEP
postgresql
Blair Anderson
Blair Anderson

Reputation: 20171

PostgreSQL Sum and Percent of Total Subquery

Table has sales and i'm trying to calculate the sales dollars and percent of total. 

WITH t1 AS (
    SELECT
    product_id,
    sum(financial_charge_cents) as cents
    FROM "account_charges"
    WHERE account_id = 5
    AND status = 'Charged'
    GROUP BY product_id
    ORDER BY cents DESC
)
SELECT 
    product_id, 
    cast(cents as money) / 100.0 AS dollars, 
    cents / (sum(cents) OVER (PARTITION BY product_id)) as percent
FROM t1
ORDER BY dollars DESC

but this is returning all the percents to be 1 and not sure why.

not sure if this is a type problem or what.

Upvotes: 1

Views: 588

Answers (1)

klin
klin

Reputation: 121604

OVER (PARTITION BY produuct_id) causes that the sums are the same as cents. You do not need partitions:

SELECT 
    product_id, 
    cast(cents as money) / 100.0 AS dollars, 
    cents* 1.0 / (sum(cents) OVER ()) as percent
FROM t1
ORDER BY dollars DESC

Note, I have added *1.0 to get numeric results. Skip it if cents are numeric (not integer).

Upvotes: 1

Related Questions