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:
Order No
- uniqueTotal Amount
- the sum of all the amount assigned to a particular order from Table ACustomer Name
- customer name from Table BBase Amount
- the "amount" with "feetype" of "baseprice" from Table ASo 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 |
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()