CODEWITHSUNDEEP
mysqlsqlderbyjavadb
Mohamed Bawaneen
Mohamed Bawaneen

Reputation: 159

nested union select statement

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.

Upvotes: 0

Views: 1223

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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

Upvotes: 2

HubertL
HubertL

Reputation: 19544

What about:

select ACCNAME, 
    CASE WHEN sum(DEBT)>sum(CREDIT) THEN sum(DEBT)-sum(CREDIT) ELSE 0 AS DEBT, 
    CASE WHEN sum(DEBT)<sum(CREDIT) THEN sum(CREDIT)-sum(DEBT) ELSE 0 AS CREDIT 
FROM (
    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
) group by ACCNAME

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269933

If you want only one row per subquery, then remove the group by:

select  'CASH' as ACCNAME, sum(amount) AS DEBT, 0 as CREDIT
FROM JOURNAL 
UNION ALL
select  'BANK' as ACCNAME, 0 as DEBT, sum(amount) AS  CREDIT
FROM JOURNAL;

(Note: the ACCNAME values might be reversed.)

Important: You should be using UNION ALL for this type of query rather than UNION. There is no reason to incur the overhead of removing duplicates, unless you intend to remove duplicates.

Also, your original query should have worked, unless there are unusual characters in the accname fields.

Upvotes: 2

Related Questions