SQL aggregate rows with same id , specific value in secondary column

Question

I'm looking to filter out rows in the database (PostgreSQL) if one of the values in the status column occurs. The idea is to sum the amount column if the unique reference only has a status equals to 1. The query should not SELECT the reference at all if it has also a status of 2 or any other status for that matter. status refers to the state of the transaction.

Current data table:

reference | amount | status
   1         100       1       
   2         120       1
   2        -120       2
   3         200       1
   3        -200       2
   4         450       1

Result:

amount | status
  550      1

I've simplified the data example but I think it gives a good idea of what I'm looking for. I'm unsuccessful in selecting only references that only have status 1. I've tried sub-queries, using the HAVING clause and other methods without success.

Thanks

hey what is your query at this stage? – EoinS Aug 31 '17 at 16:33 — EoinS, Aug 31 '17 at 16:33

score 2 · Accepted Answer · answered Aug 31 '17 at 18:39

Here's a way using not exists to sum all rows where the status is 1 and other rows with the same reference and a non 1 status do not exist.

select sum(amount) from mytable t1
where status = 1
and not exists (
    select 1 from mytable t2
    where t2.reference = t1.reference
    and t2.status <> 1
)

Damiano · Answer 2 · 2017-08-31T16:36:32.223

1

SELECT SUM(amount)
 FROM table
WHERE reference NOT IN (
 SELECT reference
 FROM table
 WHERE status<>1
)

The subquery SELECTs all references that must be excluded, then the main query sums everything except them

edited Aug 31 '17 at 16:36

answered Aug 31 '17 at 16:33

Damiano

791
1
8
22

score 1 · Answer 3 · answered Aug 31 '17 at 16:53

1

select sum (amount) as amount
from (
    select sum(amount) as amount
    from t
    group by reference
    having not bool_or(status <> 1)
) s;
 amount 
--------
    550

answered Aug 31 '17 at 16:53

Clodoaldo Neto

118,695
26
233
260

1

I like the use of `bool_or` aggregate function :) – Lukasz Szozda Aug 31 '17 at 17:05
thanks however the query results in both answers in references that have more than one status (i.e reference 2,3 ). Resulting in aggregation of all rows. Perhaps my example is not sufficient and doesn't cover the use-cases here. – OAK Aug 31 '17 at 17:07

Lukasz Szozda · Answer 4 · 2017-08-31T17:03:23.103

0

You could use windowed functions to count occurences of status different than 1 per each group:

SELECT SUM(amount) AS amount
FROM (SELECT *,COUNT(*) FILTER(WHERE status<>1) OVER(PARTITION BY reference) cnt
      FROM tc) AS sub
WHERE cnt = 0;

Rextester Demo

edited Aug 31 '17 at 17:03

answered Aug 31 '17 at 16:55

Lukasz Szozda

162,964
23
234
275

SQL aggregate rows with same id , specific value in secondary column

4 Answers4