Stock Database select question

1

I am just a 65 year old dabbler who programs as a side hobby to keep me busy and out of trouble so please don't get mad if my question is too simple.

I have developed a MYsql db using visual basic to track my stock transactions. Table tx members shown at bottom.

I have one option to list the latest transactions which does the following:

select * from tx order by tstamp desc;

I then list each row in a grid. What I would like to do, is include the total of the dividends received for each stock listed. The only way that I have figured out how to do that is to fill out the grid, and then execute the following for each row:

select sum(dividend) from tx where ticker = 'TICKER_SYMBOL';

This seems very time consuming. I feel as if there is probably a way to accomplish the same thing with a single query but I just don't see the way forward.

Thanks For any help.

Table tx looks like:
tx_type: buy sell dividend
tstamp: date and time (2021-11-30 16:59:50 for example)
Ticker:
Qty:
Dollar_per_share:
Dividend:

1 Like
2

I don't use MySQL but I'm pretty sure you should take a look at this:

What Is the SQL GROUPING SETS Clause, and How Do You Use it? | LearnSQL.com

and I think you are searching for the SQL ROLLUP function.

1 Like
3

Thanks for the response.

I tried following the rollup examples and could not seem to get the results I wanted.

After trying various methods for several days, I ended up with the result that I wanted

I am sure it isn't the most efficient and am open to critique because my main purpose for programming is to learn.

I created two temporary tables T1 and T2 and did an inner join.

Truncate Table T1
Truncate Table T2
insert into t1 select ticker, tstamp, qty, dollar_per_share from tx where tx_type = 'B' order by tstamp desc limit 50

insert into t2 select ticker,sum(dividend) dividend_total from tx group by ticker

SELECT ticker, tstamp, dollar_per_share, qty, t2.dividend FROM t1 inner join t2 using (ticker) order by tstamp desc

This gives me a list of the last buys in descending order, showing me the total dividends for each stock that I purchased.