Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Unanswered: Enhance SQL Script

    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

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •