I have a table in MySQL that lists stock purchases of a user (so there are multiple rows of the same stock with different prices and quantities).
I'd like to know how I can list the unique stocks with just the sum of all the stocks and total amount spent on that stock.
So for example, here's the original table,
+------------+--------+-----------+
| ticker | shares | price |
+------------+--------+-----------+
| AMARAJABAT | 5 | 720.0000 |
| AMARAJABAT | 5 | 732.0000 |
| ENDURANCE | 2 | 1650.0000 |
| ENDURANCE | 2 | 1750.0000 |
| EXIDEIND | 21 | 163.5000 |
| EXIDEIND | 21 | 172.1000 |
+------------+--------+-----------+
and a select of the table to get the shares*price,
SELECT ticker,shares,price,shares*price AS spent FROM bought ORDER BY ticker;
+------------+--------+-----------+------------+
| ticker | shares | price | spent |
+------------+--------+-----------+------------+
| AMARAJABAT | 5 | 720.0000 | 3600.0000 |
| AMARAJABAT | 5 | 732.0000 | 3660.0000 |
| ENDURANCE | 2 | 1650.0000 | 3300.0000 |
| ENDURANCE | 2 | 1750.0000 | 3500.0000 |
| EXIDEIND | 21 | 163.5000 | 3433.5000 |
| EXIDEIND | 21 | 172.1000 | 3614.1000 |
+------------+--------+-----------+------------+
And I'd like to actually sum the values of the shares columns (for the same stock) and the amount spent on each. i.e an output of,
+------------+-------------+------------+
| ticker | SUM(shares) | SUM(spent) |
+------------+-------------+------------+
| AMARAJABAT | 10 | 7260.0000 |
| ENDURANCE | 4 | 6800.0000 |
| EXIDEIND | 42 | 7047.6000 |
+------------+-------------+------------+
but can't seem to figure out how I can sum multiple columns and rows by ticker.
Thanks in advance!
Sum shares and shares * price and group by ticker.
SELECT ticker, SUM(shares) AS 'Total Shares', SUM(shares * price) AS `Total Spent`
FROM bought
GROUP BY ticker
ORDER BY ticker;