Enhance SQL Script

jplsql · #1 (**permalink**) 01-23-04, 12:59

I would like to enhance this sql script by being able to do a total amount on sum(u.amount). How can this be done?

select b.borrower#, sum(u.amount)

from burb u, borrower b

where b.borrower# = u.borrower#

and expiration_date >10226

group by b.borrower#

having sum(u.amount) > 10000

r123456 · #2 (**permalink**) 01-23-04, 21:46

select b.borrower#, sum(u.amount), (select sum(u.amount) from burb) As Total
from burb u, borrower b
where b.borrower# = u.borrower#
and expiration_date >10226
group by b.borrower#
having sum(u.amount) > 10000

joebednarz · #3 (**permalink**) 02-02-04, 11:46

Here's an idea...

Code:

SELECT   b.borrower#, SUM (u.amount)
    FROM burb u, borrower b
   WHERE b.borrower# = u.borrower# AND expiration_date > 10226
GROUP BY b.borrower#
  HAVING SUM (u.amount) > 10000

UNION ALL

SELECT NULL, SUM (u.amount)
  FROM burb

This way, the "Total" is displayed only once and at the bottom of the list.

NOTE: You'll have to resist the temptation to do this "SELECT 'Total', SUM( u.amount )..." because when you do a UNION / UNION ALL, the column data types have to match. I'm assuming the "borrower#" is a numeric, therefore putting text in the "Total" row will give you an error.

JoeB

r937 · #4 (**permalink**) 02-02-04, 12:20

both the scalar subquery and union all solutions forgot both the WHERE and the HAVING conditions, and will therefore produce incorrect results

yeah, i know, picky, picky...

Internet Services

Web Development

Digital Marketing

Consumer Tech