sqlms-access

Query with sum but reference a value from the same calculation in a different column


I have two tables, Table A being:

orderNo feetype amount
1 shipping -10
1 baseprice 100
1 commission -5
1 discount -20
2 commission -10
2 discount -10
2 shipping -20
2 baseprice 150

and Table B:

orderNo customer item
1 John beer
1 John soda
2 Mary cake
2 Mary coffee
2 Mary pie

I need a query to generate a table with the following columns:

  1. Order No - unique
  2. Total Amount - the sum of all the amount assigned to a particular order from Table A
  3. Customer Name - customer name from Table B
  4. Base Amount - the "amount" with "feetype" of "baseprice" from Table A

So far I've managed to generate columns 1 to 3 with the following query:

SELECT
    c.customer, a.orderNo, Sum(a.amount) AS [total]
FROM 
    a
INNER JOIN 
    (SELECT DISTINCT orderNo, customer 
     FROM table b) AS c ON a.orderNo = c.orderNo
GROUP BY 
    c.customer, a.orderNo;

But what I need is the following table

orderNo customer baseprice total
1 John 100 65
2 Mary 150 110

Solution

  • For MS Access you need to use something like this:

    SELECT a.orderNo,
           b.customer,
           SUM(IIF(a.feetype = 'baseprice', a.amount, 0)) AS baseprice,
           SUM(a.amount) AS Total
    FROM (TableA AS a
    INNER JOIN (
        SELECT DISTINCT orderNo, customer 
        FROM TableB
    ) b ON a.orderNo = b.orderNo)
    GROUP BY a.orderNo, b.customer;
    

    The idea is similar to @Ergest Basha's solution but uses IIF(,,) instead of CASE WHEN and I prefer SELECT DISTINCT to SELECT MAX()