locked
sql select sum per field form 2 tables RRS feed

  • Question

  • User954514883 posted

    Hi all,

    I have my data stored in 2 tables, year 1 and year2.
    I want to have the totals per destination, per customer, per year.

    customer   dest     turnover Table1     turnover Table2
    1           JNB             100                       0
    2           JNB             200                       300
    1           DUR             0                         50
    1           DUR             10                        60


    output needed
    dest:                       year1    year2
           DUR
           customer1             10       110
           customer2             0        0

           JNB
           customer1             100      0
           customer2             200      300

    Can somebody help me please how to make the select string?

    Thanks in advance

    Jan Prins

    Wednesday, March 19, 2014 10:46 AM

All replies

  • User-484054684 posted

    If customer+dest combination is a primary key, then, the following query should work.

    If it is not, then, removing the duplicate rows from joins is required I believe.

    select 
        year1.dest1, 
        year1.customer1, 
        sum(year1.turnover1) as year1TurnOver, 
        sum(year2.turnover2) as year2TurnOver 
    from year1 
    join year2 on year1.customer1 = year2.customer2 and year1.dest1 = year2.dest2
    group by year1.dest1, year1.customer1

    Demo

    Wednesday, March 19, 2014 12:50 PM
  • User954514883 posted

    Thank you Siva!

    Thursday, March 20, 2014 3:36 PM