SQL. Combining two queries with aggregate functions and WHERE conditions

Question

I am relatively new to SQL and ran into a problem I can't seem to solve. I am using Access and I tried to work something with IIF, but that doesn't help or I don't know how.

I have got 2 queries that work fine on their own, how can I combine them into one? My biggest problem seems to be to me that I have two WHERE statements that I cannot seem to get into one query.

SELECT CODE, SUM(Costs)/SUM(Quantity) AS SIMPLE_COST_BASE 
FROM Shares 
WHERE EVENT = 'Buy' 
GROUP BY CODE;

SELECT CODE, (SUM(Costs) + SUM(DRP_PAY))/SUM(Quantity) AS NORMAL_COST_BASE 
FROM Shares 
WHERE EVENT <> 'Sell' 
GROUP BY CODE;

My desired result is a table with three columns. One for CODE, one for the SIMPLE_COST_BASE and a third for NORMAL_COST_BASE

Please add some example data and your expected result. And I think you can use `UNION` ;). — shA.t, Oct 03 '15 at 08:26

score 1 · Answer 1 · answered Oct 03 '15 at 09:28

1

I think something like this should work:

SELECT Shares.CODE, Sum(IIf([EVENT]<>"Buy",[Costs]/[Quantity],0)) AS SIMPLE_COST_BASE,
Sum(IIf([EVENT]<>"Sell",[DRP_PAY]/[Quantity],0)) AS NORMAL_COST_BASE
FROM Shares
GROUP BY Shares.CODE;

answered Oct 03 '15 at 09:28

Sergey S.

6,296
1
14
29

Thanks Sergey. Unfortunately, the values calculated don't seem right. I am not quite sure why and am still trying to understand it. – JoeK Oct 03 '15 at 11:12
If you give an example of data and expected result, we will find why it doesn't work as expected. – Sergey S. Oct 04 '15 at 05:06
Thanks Sergey, these are some example rows CODE / EVENT / Quantity / COSTS / DRP-Pay CCV; Buy; 1000; 965; 0 CCV; Buy, 1800; 1980; 0 CCV; DRP; 60; 0; 55.80 CCV; DRP; 53; 0; 56.99 CCV; DRP; 65; 0; 58.50 The values I would be expecting for the three columns are: CODE: CCV SIMPLE_COST_BASE: 1.0517 NORMAL_COST_BASE: 0.9889 – JoeK Oct 08 '15 at 08:47

score 0 · Answer 2 · edited May 23 '17 at 12:14

If it is certain that both queries will return the same number of rows with the same codes (both return "all" codes), you can simply JOIN them as subqueries:

SELECT buy.CODE, buy.SIMPLE_COST_BASE, notsell.NORMAL_COST_BASE
FROM
(
    (SELECT CODE, SUM(Costs)/SUM(Quantity) AS SIMPLE_COST_BASE 
    FROM Shares 
    WHERE EVENT = 'Buy' 
    GROUP BY CODE) AS buy

    INNER JOIN

    (SELECT CODE, (SUM(Costs) + SUM(DRP_PAY))/SUM(Quantity) AS NORMAL_COST_BASE 
    FROM Shares 
    WHERE EVENT <> 'Sell' 
    GROUP BY CODE) AS notsell

    ON buy.CODE = notsell.CODE
)

If some codes may be in only one of the query result sets, you should save both subqueries as separate Access queries, and then do a FULL OUTER JOIN simulation of them with the technique described here: How do I write a full outer join query in access

SQL. Combining two queries with aggregate functions and WHERE conditions

2 Answers2