sqlaggregate-functionssybase

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

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: Table RECORDS

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.)

Solution

  • Assumptions:

    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:

    This also generates:

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

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