Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: join 2 results but not in rows

    hi,
    i have simple question,
    how can i join 2 results from 1 table
    but the result should be in a new field not in rows
    i tried this script
    Code:
    select
    (
    select
    sum(amountfigures) 
    from banktransact_tbl
    where transacttype = 'deposit' 
    group by accountname ) as 'Cash On Hand'
    , ( select
    sum(amountfigures)
    from banktransact_tbl
    where transacttype = 'deposit' 
    group by
     branch ) as 'Cash Out'
    from banktransact_tbl;
    error
    Code:
    Subquery returns more than 1 row
    id like to ask help here

    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no idea what you're trying to do

    why don't you show us a few sample rows of data in that table, then show the results that you want the query to produce from that sample
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    ok,
    the first select
    Code:
    select
    accountname
    , sum(amountfigures) as 'Cash on Hand'
    , transacttype
    from banktransact_tbl
    where transacttype = 'deposit' 
    group by accountname
    result
    Code:
    'BRANCH', 'CASH ON HAND', 'TRANSACTTYPE'
    'MBSC-001', 36956775.28, 'Deposit'
    'MGC-888', 13404839.00, 'Deposit'
    and the second select
    Code:
    select
    branch
    , sum(amountfigures) as 'Cash Out'
    , transacttype
    from banktransact_tbl
    where transacttype = 'deposit' 
    group by
     branch;
    result
    Code:
    'BRANCH', 'CASH OUT', 'TRANSACTTYPE'
    'MBSC-001', 250827.00, 'Deposit'
    'MGC-888', 50110787.28, 'Deposit'
    i need the 'Cash Out' and 'Cash on Hand' in one row
    Code:
    'BRANCH','CASH ON HAND', 'CASH OUT', 'TRANSACTTYPE'
    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT accountname AS branch
         , SUM(subtotal1) AS 'Cash on Hand'
         , SUM(subtotal2) AS 'Cash Out'
         , transacttype
      FROM (
           SELECT accountname
                , SUM(amountfigures) AS subtotal1
                , 0                  AS subtotal2
                , transacttype
             FROM banktransact_tbl
            WHERE transacttype = 'deposit' 
           GROUP 
               BY accountname
           UNION ALL
           SELECT branch
                , 0 
                , sum(amountfigures) 
                , transacttype
             FROM banktransact_tbl
            WHERE transacttype = 'deposit' 
           GROUP 
               BY branch
           ) AS u
    GROUP
        BY accountname 
         , transacttype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    i forgot... (",)

    what i did is
    I make VIEW Tables for each SELECT Statement
    and JOIN them, but its not too practical
    so im going to use your script

    great, thanks for the help!
    Last edited by homer.favenir; 03-15-09 at 06:50.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

Posting Permissions

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