CODEWITHSUNDEEP
sqlms-access
JoeK
JoeK

Reputation: 1

SQL. Combining two queries with aggregate functions and WHERE conditions

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

Upvotes: 0

Views: 1370

Answers (2)

Andre
Andre

Reputation: 27634

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

Upvotes: 0

Sergey S.
Sergey S.

Reputation: 6336

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;

Upvotes: 1

Related Questions