Postgres - Get sum of the values in a column on condition of another column

Question

I have a table in Postgres (custdata) that looks like this:

customerid | mID | count
    1         3      13
    1         2      17
    1         2       3
    1         5      14
    1         4       7

And a query like this:

SELECT
c.customerid,
c.mID,
c.count,
SUM (c.count)
FROM custdata c
JOIN tableB b
ON b.customerid = c.customerid
WHERE c.mID <> 2
AND b.starttimestamp = ? and b.endtimestamp = ?

I want to get the sum of the values in the count column of the values whose mID does not equal 2. In this case, the correct sum would be 34. But in my case, the result is returning 2 different rows with the incorrect sum. How can I get the sum of the values in the count column where the mID is not 2? Any help would be appreciated.

If your result is 2 rows, then you used GROUP BY. Also, your query is invalid without a GROUP BY. Please post the query you ran & clarify your expected results. (F.ex. should the sum be calculated per customerids?) — pozs
– pozs, Commented Mar 13, 2017 at 14:41

Tajinder · Accepted Answer · 2017-03-12 19:47:17Z

2

Hoping, i understood correctly. Please check below query

SELECT
c.customerid,
c.mID,
c.count,
SUM(case when c.mID<>2 then  c.count else 0  end) over(partition by c.customerid order by c.customerid) sum_col
FROM custdata c
JOIN tableB b
ON b.customerid = c.customerid
WHERE
--c.mID <> 2 AND
b.starttimestamp = ? and b.endtimestamp = ?

answered Mar 12, 2017 at 19:47

Tajinder

2,3484 gold badges38 silver badges58 bronze badges

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

Comments

light souls · Accepted Answer · 2017-03-12 19:45:27Z

1

With window function you can group with customerID:

SELECT
c.customerid,
c.mID,
c.count,
SUM(c.count) over(partition by c.customerid)
FROM custdata c
JOIN tableB b using(customerid)
WHERE c.mID <> 2
group by c.customerid,
c.mID,
c.count

answered Mar 12, 2017 at 19:45

light souls

7381 gold badge9 silver badges18 bronze badges

Collectives™ on Stack Overflow

Postgres - Get sum of the values in a column on condition of another column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related