Calculating and Adding Several Columns to SQL results in only 1 row-CodePudding

I have a SQLite DB that has a table which has the following values:

id	Broker	Stock	Long_Short	Stock_Price	Shares_Owned
1	1	AMC	Long	14.76	8
2	1	MSFT	Short	282.36	8
3	1	AAPL	Short	161.42	8
4	1	TSLA	Short	922.672	8
5	1	FB	Long	215.48	8
6	1	XOM	Short	91.72	8
7	1	BAC	Long	37.8	8
8	1	KO	Short	64.745	8
9	1	DIS	Long	114.42	8
10	1	VZ	Short	48.02	8
11	2	AMC	Long	14.76	9
12	3	AMC	Short	14.76	10

I need to be able to query the table to get the following result:

Stock | Aggregate_Value | Total_Added_Up | "Exposure %"

Aggregate_Value = Stock_Price * Shares_Owned
Total_Added_Up = The sum of all Aggregate_Value's.
"Exposure %" = ((Aggregate_Value / Total_Added_Up) * 100)

select Stock, SUM(Total_Value) AS Aggregate_Value from (select Stock, Stock_Price*Shares_Owned AS Total_Value from Trades ) group by Stock;

The above query gives me a good Aggregate_Value column.

I can get the Total_Added_Up value by doing:

    from (select Stock, SUM(Total_Value) AS Aggregate_Value
        from (select Stock, Stock_Price*Shares_Owned AS Total_Value 
            from Trades)
            group by Stock);

I tried the following query to combine Aggregate_Value Total_Added_Up, and "Exposure %":

    from (select Stock, Aggregate_Value, SUM(Aggregate_Value) AS Total_Added_Up
        from (select Stock, SUM(Total_Value) AS Aggregate_Value
            from (select Stock, Stock_Price*Shares_Owned AS Total_Value 
                from Trades)
                group by Stock));

The above query will give me the format Stock | Aggregate_Value | Total_Added_Up | "Exposure %", but it is only one row when there should be a row for every Stock.

The output I am getting looks like the following.

Stock	Aggregate_Value	Total_Added_Up	Shares_Owned
XOM	733.76	15907.616	4.6126333

The output I am expecting is.

Stock	Aggregate_Value	Total_Added_Up	Shares_Owned
AMC	733.76	15907.616	4.6126333
MSFT	(To_Calculate)	15907.616	(To_Calculate)
AAPL	(To_Calculate)	15907.616	(To_Calculate)
TSLA	(To_Calculate)	15907.616	(To_Calculate)

And so on with the rest of the listed Stocks.

Am I overcomplicating this or is there an easier way to get to my solution?

CodePudding user response：

select Stock, SUM(Total_Value) AS Aggregate_Value from (select Stock, Stock_Price*Shares_Owned AS Total_Value from Trades group by Stock;)

Can be converted to

SELECT Stock, 
     SUM(Stock_Price*Shares_Owned) AS Aggregate_Value 
     FROM Trades 
GROUP BY Stock;

What you're doing is correct, just make it look cleaner, it'll help you read and debug it.

This will give you the Total_Added_Up value. It's only a variable. I am not familiar with SQLite DB, but it might be difficult to use a variable there.

SELECT SUM(Stock_Price*Shares_Owned) 
FROM Trades

So join those two tables in a new select and you have all of the needed values for the Exposure value. Just make sure to not divide by 0 in your formula

CodePudding user response：

You can do this in one seLect by joning the total sum

I also rounded it uop to 2 digits, so that ot wouldn't look to wild, but you can increase the numbers of digits or get rod of the rounding

SELECT
`Stock`,
SUM(`Stock_Price` * `Shares_Owned`)
,Total_Added_Up, ROUND((SUM(`Stock_Price` * `Shares_Owned`) / Total_Added_Up) * 100,2) as 'Exposure %'
FROM stocks CROSS JOIN (SELECT SUM(`Stock_Price` * `Shares_Owned`) Total_Added_Up  FROM stocks) as t1
GROUP BY `Stock`

Stock | SUM(`Stock_Price` * `Shares_Owned`) | Total_Added_Up | Exposure %
:---- | ----------------------------------: | -------------: | ---------:
AAPL  |                             1291.36 |      15907.616 |       8.12
AMC   |                              398.52 |      15907.616 |       2.51
BAC   |                               302.4 |      15907.616 |        1.9
DIS   |                              915.36 |      15907.616 |       5.75
FB    |                             1723.84 |      15907.616 |      10.84
KO    |                              517.96 |      15907.616 |       3.26
MSFT  |                             2258.88 |      15907.616 |       14.2
TSLA  |                            7381.376 |      15907.616 |       46.4
VZ    |                              384.16 |      15907.616 |       2.41
XOM   |                              733.76 |      15907.616 |       4.61

db<>fiddle here