I am in MySQL, trying to join two SELECT queries into one table, but not getting the complete result as needed.
The data (let's say those are some transactions):
| id | unit | price | name | type |
|---|---|---|---|---|
| g6598r6e3w3 | 2 | 300 | US123456 | buy |
| db376517682 | 1 | 500 | US123456 | sell |
| yd659cf893d | 3 | 150 | US846215 | buy |
| at5k2ca676z | 1 | 100 | US846215 | buy |
| g5s8e44r65r | 2 | 250 | US846215 | sell |
I would like to get following result. Basicaly: for every name, get total number of sell units, total number of buy units and total price for both buy and sell.
The desired output:
| name | unitsBuy | unitsSell | totalBuyPrice | totalSellPrice |
|---|---|---|---|---|
| US123456 | 2 | 1 | 600 | 500 |
| US846215 | 4 | 2 | 550 | 500 |
...all in single MySQL query. My current approach is to get all sell and buy transactions separately and then join them with LEFT JOIN. But that gives mi mixed results, sometimes half-empty, depending on number of transactions for each name.
What approach would you suggest?
Thanks
