join 2 results but not in rows

homer.favenir · #1 (**permalink**) 03-15-09, 04:16

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

r937 · #2 (**permalink**) 03-15-09, 04:38

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

homer.favenir · #3 (**permalink**) 03-15-09, 05:10

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

r937 · #4 (**permalink**) 03-15-09, 05:28

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

homer.favenir · #5 (**permalink**) 03-15-09, 05:46

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech