How do I get this query to display the relevant data for all symbols without needing it to input?-CodePudding

I am trying to create an advanced query that requires sub-queries IN MS Access. I have managed to get the query to work but only if I input the specific WHERE and HAVING clause.

Here is the table I am using: Mock Trades Table

This is what the query looks like: My attempt at the query (I entered EURUSD)

SELECT Symbol, 
(SELECT SUM([Lot Size]) FROM [Mock Trades] WHERE [Trade Type] = "Buy" AND Symbol = [Input];) AS [Buy Lot], 
(SELECT SUM([Lot Size]) FROM [Mock Trades] WHERE [Trade Type] = "Sell" AND Symbol = [Input];) AS [Sell Lot],
(SELECT SUM([Lot Size]) FROM [Mock Trades] WHERE Symbol = [Input];) AS [Net Lots],
(SELECT SUM([Profit]) FROM [Mock Trades] WHERE [Trade Type] = "Buy" AND Symbol = [Input];) AS [Buy Profits], 
(SELECT SUM([Profit]) FROM [Mock Trades] WHERE [Trade Type] = "Sell" AND Symbol = [Input];) AS [Sell Profits], 
(SELECT SUM([Profit]) FROM [Mock Trades] WHERE Symbol = [Input];) AS [Net Profits]
FROM [Mock Trades]
GROUP BY Symbol
HAVING Symbol = [Input];

This makes the query only display the data for that specific input but I want it to show for every other possible input and have it calculate each column correctly. I tried implementing a GROUP BY in each embedded SELECT but it gave me an error.

I made a mock table to show what I want the query to look like: The ideal result

Any assistance would be greatly appreciated!

CodePudding user response：

You can do it the following way:

    SELECT Symbol, 
    (SELECT SUM([Lot Size]) FROM [Mock Trades] T2
 WHERE T2.[Trade Type] = "Buy" 
AND T2.Symbol = T1.symbol) AS [Buy Lot]
    FROM [Mock Trades] t1
    GROUP BY Symbol

note: I have removed the semicolon, you can place it back if the query actually runs like that.