locked
complex -Combine SQL query with Group Statement RRS feed

  • Question

  • User-1967952962 posted

    Hi,

            I have a result from

    table -1 : condition date = 02/15/2017

    id

    date

    testno

    bamount

    camount

    aname

    cname

    1

    02/15/2017

    45677

    0.00

    0.00

    ABC

    ABC-1234

    2

    02/16/2017

    341

    0.00

    2000.00

    DFG

    DFG-123

    3

    02/17/2017

    2555

    12.00

    10045.23

    SDF

    SDF-12345

    Table-2  :  condition sum and date <= 02/15/2017

    btot

    date2

    aname

    cname

    600

    2/18/2017

    ABC

    ABC-1234

    10

    2/15/2017

    ABC

    ABC-1234

    20

    DFG

    DFG-123

    500

    2/14/2017

    ABC

    ABC-1234

    50

    DFG

    DFG-123

    -120

    2/12/2017

    ABC

    ABC-1234

    1000

    SDF

    SDF-12345

    And I want the result to Combine table1 and Sum of table-2 

    and result  like the following 

    id

    testno

    bamount

    camount

    aname

    cname

    Btot-SUM

    date-3

    1

    45677

    0.00

    0.00

    ABC

    ABC-1234

    390

    2/15/2017

    2

    341

    0.00

    2000.00

    DFG

    DFG-123

    70

    3

    2555

    12.00

    10045.23

    SDF

    SDF-12345

    1000

    Any help appreciated thank you.

    Monday, February 26, 2018 10:23 PM

Answers

  • User-1716253493 posted
    select A.*, B.[btot-sum],B.[date-3] form 
    (select * from table1) A 
    inner join 
    (select aname, sum(btot) as [btot-sum], max(date-2) as [date-3] from table2 
    where date2<= '02/15/2017' group by aname) B ON A.aname=B.aname

    correct it if typo error

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 27, 2018 12:55 AM