sum column from child table twice base on another column RRS feed

  • Question

  • so i have an order table as parent and shipments table as child with ct column in each table (ct is number of containers)

    when the arrived column is filled with a date it means that the shipment arrived but if not it means it is still on the way

    so i want to make a query to get a table like this

    ct ordered ------- contents ----------ct arrived---------ct remaining

    i only can do sum once to retrieve sum of the ct from the child table

    i want to do it twice first to sum all the shipemnts that arrived and the second to sum all the shipments that didnt arrived based on a column lets call it arrived which contain date if arrived,empty if not yet

    I am A Medical Doctor

    Thursday, July 12, 2018 9:53 AM


  • Use expressions like this in the query

    SUM(ct * IIF(Arrived IS NULL, 0,1)) AS [ct arrived],
    SUM(ct * IIF(Arrived IS NULL, 1,0)) AS [ct remaining]

    Ken Sheridan, Stafford, England

    Thursday, July 12, 2018 4:44 PM