sybase sql: choose records for which sum of values in column XYZ equals some specific value

Question

Using Sybase SQL, I want to find all records fulffilling certain simple requirements (such as DEBITCREDIT == 'D') AND the following requirement: the sum of the nominal values of these records must equal 300

Specifically, I want to do something like this:

select RECORD_ID   ,
       BOOKING_ID
  from RECORDS          
 where SECURITIES_ID       = 'B'   
   and DEBITCREDIT         = 'D'
   and sum(NOMINAL_VALUE)  = 300 -- NOT ALLOWED!

        

The problem is that this is not allowed. How can I achieve my goal in a way that is allowed and works?

... Here is a screenshot as an example:

In the screenhot the purpose of my select statement would be to select the RECORD_IDs highlighted in yellow, namely RECORD_ID 2 and RECORD_ID 4. (Please note that NOMINAL_VALUE of RECORD_ID 2 + NOMINAL_VALUE of RECORD_ID 4 = 300.)

Answer 1

Assumptions:

• sum(NOMINAL_VALUE)=300 refers to all rows where SECURITIES_ID='B' and DEBITCREDIT='D'
• we are NOT looking for a subset of rows (SECURITIES_ID='B' and DEBITCREDIT='D') where sum(NOMINAL_VALUE)=300; for example ...
• if we were looking for sum(NOMINAL_VALUE)=100 then we would return nothing (ie, we would not return RECORD_ID=2)

Create/populate a sample table:

create table RECORDS
(RECORD_ID      int
,SECURITIES_ID  char(1)
,DEBITCREDIT    char(1)
,NOMINAL_VALUE  int
,BOOKING_ID     int
)
go

insert RECORDS values (1,'A','C',100,10)
insert RECORDS values (2,'B','D',100,20)
insert RECORDS values (3,'B','C',100,30)
insert RECORDS values (4,'B','D',200,40)
insert RECORDS values (5,'C','D', 50,50)
go

There are a few ways to slice-n-dice this ...

---

Using a subquery with no explicit join to the parent:

select  r.RECORD_ID,
        r.BOOKING_ID
from    RECORDS r
where   r.SECURITIES_ID = 'B'
and     r.DEBITCREDIT   = 'D'
and     300 = (select   sum(r2.NOMINAL_VALUE)
                 from   RECORDS r2
                 where  r2.SECURITIES_ID = 'B'
                 and    r2.DEBITCREDIT   = 'D')
order by 1,2

NOTE: there's no explicit 'join' criteria so OP needs to be very careful to insure both the parent query and subquery reference the same exact set of rows

This generates:

 RECORD_ID   BOOKING_ID
 ----------- -----------
           2          20
           4          40

---

Modifying the previous query by a) converting the subquery to a derived table and b) joining the parent to the derived table via the SECURITES_ID and DEBITCREDIT columns:

select  r.RECORD_ID,
        r.BOOKING_ID
from    RECORDS r
join    (select r2.SECURITIES_ID,
                r2.DEBITCREDIT,
                sum(r2.NOMINAL_VALUE)
         from   RECORDS r2
         where  r2.SECURITIES_ID = 'B'
         and    r2.DEBITCREDIT   = 'D'
         group by r2.SECURITIES_ID,
                  r2.DEBITCREDIT
         having sum(r2.NOMINAL_VALUE) = 300) dt
on      r.SECURITIES_ID = dt.SECURITIES_ID
and     r.DEBITCREDIT   = dt.DEBITCREDIT
order by 1,2

This also generates:

 RECORD_ID   BOOKING_ID
 ----------- -----------
           2          20
           4          40

---

If this is Sybase ASE we can make use of its support of non-ANSI compliant SQL with this really fugly query:

select  RECORD_ID,
        BOOKING_ID
from    RECORDS
where   SECURITIES_ID = 'B'
and     DEBITCREDIT   = 'D'
group by SECURITIES_ID, 
         DEBITCREDIT
having sum(NOMINAL_VALUE) = 300
order by 1,2

NOTES:

• the query plan for this is similar to the query plan for the other queries, ie, it requires two accesses of the RECORDS table
• while it's tempting to use this 'simpler' non-ANSI compliant SQL query, I'd recommend one of the other queries as they'll be easier to understand (especially for someone who comes across this code at a later date)

This also generates:

 RECORD_ID   BOOKING_ID
 ----------- -----------
           2          20
           4          40

---

NOTE: all queries were run on an ASE 16.0 SP04 PL04 instance