nested union select statement

Question

Is it possible to group result of my sql SELECT statements without creating temp table to sum and group in one statement? I have a table saving the total of debit and credit amount for credit account No. and debit account No. and put zero for opposite field, I made first grouppage with below union select statement and I need to sum the output based on repeated accounts name I can get this the same easily or have to load it temp table? my select statement as below

select  journal_drname  AS ACCNAME, sum(amount) AS DEBT,0 as CREDIT
FROM JOURNAL GROUP BY journal_drname 
 UNION select  journal_crname  AS ACCNAME, 0 as DEBT,sum(amount) AS   
 CREDIT,
 FROM JOURNAL GROUP BY journal_crname

returning data like:

ACCNAME      DEBIT     CREDIT
--------   -------     ------
CASH        0           1,000
CASH        900          0
CASH        300          0
BANK        200          0
BANK        400          0

can I summery above to be grouped as follows:

ACCNAME      DEBIT     CREDIT
--------   -------     ------
CASH        200          0
BANK        600          0

And I want to join the results of these to be cut as above.

I'm trying to avoid using a temp table, but if that's the only practical way to do this, I'd like to know more about using temp tables in this fashion.

I also tried to do some kind join and union but not satisfy a conditional and I couldn't figure out how to do that either.

Answer 1

As you say your query is working, lets call your union query as a table

( ...... ) myTable

then just create a subquery to get the final step

SELECT ACCNAME,
       CASE WHEN SUM(CREDIT - DEBT) < 0 THEN - SUM(CREDIT - DEBT)
            ELSE 0
       END DEBT,
       CASE WHEN SUM(CREDIT - DEBT) > 0 THEN   SUM(CREDIT - DEBT)
            ELSE 0
       END CREDIT
FROM ( ...... ) myTable
GROUP BY ACCNAME