Postgresql conditional sum

Question

I have a table with the following data for sales and inventory (oh):

category sales  oh  item_num
Clothes  12     10  1
Clothes  11     10  1
Clothes  10     10  1
Clothes  5      10  1
Clothes  8      10  1
Clothes  4      10  1
Clothes  23     10  2
Clothes  5      10  2
Clothes  20     10  2
Clothes  5      10  2
Clothes  13     10  2
Clothes  9      10  2
Food     6      25  3
Food     8      25  3
Food     7      25  3
Food     14     25  3

I am trying to query this table to get a sum of both the sales and oh columns by category:

SELECT category, SUM(sales) AS sales, SUM(oh) AS oh
FROM data
GROUP BY category

However, the problem is I need the SUM(oh) to only sum distinct items but the SUM(sales) to sum all the values. So the result should be:

category   sales   oh
Clothes     125    20
Food        35     25

I tried SUM(DISTINCT oh), but that only works for distinct oh values not distinct items. I really need something like SUM(DISTINCT(item_num) oh).

I experimented with various window functions, but could not come up with a solution. Does anyone know how to return this kind of sum on a unique key?

I guess I'm a little puzzled still. Are you saying that oh values are dependent on item_num and thus have guaranteed fixed values for specific item_num values? If not, how would one choose? — Feneric
– Feneric, Commented Aug 8, 2018 at 15:32
Yes, each item_num has a single oh (# of items on hand in the store) at the end of the period, but each item_num will have several sales records during the period. For example item 1 might be a blue shirt, item 2 might be a red scarf, and item 3 might be a candy bar. The query should tell us how many total items were sold for each category and how many items we have in total for each category at the end of the period. — user2662201
– user2662201, Commented Aug 8, 2018 at 18:20

Michael Powers · Accepted Answer · 2018-08-08 18:37:04Z

1

Here's how I'd do it:

SELECT category, SUM(sales) AS sales, SUM(oh) AS oh
FROM (
    SELECT category, SUM(sales) AS sales, oh
    FROM data
    GROUP BY category, item_num, oh
) ttl
GROUP BY category;

Basically tackle the problem in stages. First group up the items by category and item number to get the sum of sales then group and sum by category to get the sum of oh.

Result:

 category | sales | oh
----------+-------+----
 Food     |    35 | 25
 Clothes  |   125 | 20
(2 rows)

Edit: Included simplified query.

edited Aug 8, 2018 at 18:37

answered Aug 8, 2018 at 15:42

Michael Powers

2,0601 gold badge9 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2662201 Over a year ago

Thanks, that works great. I also found it is possible to do this with just one subquery by using: SELECT category, SUM(sales) AS sales, SUM(oh) AS OH FROM (SELECT category, SUM(sales) AS sales, oh FROM data GROUP BY category, item_num, oh) AS ttl. I really appreciate your time in posting the solution.

Michael Powers Over a year ago

Edited my answer to include the simplified query.

Collectives™ on Stack Overflow

Postgresql conditional sum

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related