Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Jul 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    get sum value from two tables

    hi guys,

    I have two table name tblhistory and tbltransaction. Both table have column name terminalno and clearingdate. I want get the transaction count based on clearing date and group by terminalno.

    here is my coding
    Code:
    select TerminalNo, count (1) as COUNT from tbltransaction where 
    month(clearingdate) = Month('20110601')
    and year(clearingdate) = year ('20110601')
    group by terminalno
    
    UNION ALL
    
    select TerminalNo, count(1) as COUNT from tblhistory where 
    month(clearingdate) = month ('20110601')
    and year(clearingdate) = year ('20110601')
    group by terminalno
    order by terminalno asc
    example result from this codes:
    Terminalno COUNT
    V005------201
    V005------298
    V007------122
    V007------110

    BUT

    how to get the result like this:
    Terminalno COUNT
    V005------499
    V007------232

    really appreciate any help.Thank you

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    A couple of ways to do this.

    But *probably* best performance to do this:
    Code:
    SELECT U.TerminalNo, SUM(U.theCount) AS COUNT
    FROM (
        select TerminalNo, count (*) as theCount 
        from tbltransaction 
        where month(clearingdate) = Month('20110601') and year(clearingdate) = year ('20110601')
        group by terminalno
        UNION ALL
        select TerminalNo, count(*)
        from tblhistory 
        where month(clearingdate) = month ('20110601') and year(clearingdate) = year ('20110601')
        group by terminalno
        ) AS U
    GROUP BY terminalno
    You COULD do it as
    Code:
    SELECT U.TerminalNo, COUNT(*) AS COUNT
    FROM (
        select TerminalNo 
        from tbltransaction 
        where month(clearingdate) = Month('20110601') and year(clearingdate) = year ('20110601')
        group by terminalno
        UNION ALL
        select TerminalNo
        from tblhistory 
        where month(clearingdate) = month ('20110601') and year(clearingdate) = year ('20110601')
        group by terminalno
        ) AS U
    GROUP BY U.TerminalNo
    But I think you can see that this UNION produces many more records as the intermediate step.

    Still, it might be worth trying it both ways to see which performs better.

  • #3
    New to the CF scene
    Join Date
    Jul 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks old pedant.

    the first one get the result as i want.
    actually i has tried that similar method but got wrong somewhere.

    thanks again.


  •  

    Posting Permissions

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