Short backstory for this question. I am a student who works part-time as a tutor for several classes, including an entry level SQL class. I’ve got a student who has a fairly complicated database, compared to what most people get out of this class. Since their database ins’t super simple, they’e run into a problem trying to get a number. I’m trying to think of not necessarily the most efficient way for them to solve it, but the best way to show someone still learning some of the basics. I have a rough idea, but wanted help from here. If this is not allowed, sorry I will delete it. Assuming it is allowed:
So I’ve already had them consolidate two of their tables into one, that way they would not need to use joins to solve this. Now their new table has the following columns: holdingID, clientID, shares, price (I’m omitting what’s not necessary to get the number for readability). They want to multiply shares by price to get a new total column, and then group that total column by clientID to show how much in holdings each client has in total between all of what they have stocks in. I am thinking of having them use a sub-query to do the multiplication and then doing a sum(total) with a group by clientID to get the number they want. I’m coming here because, they actually don’t learn about sub-queries until next semester, and I don’t want to overload them/make their professor wonder why they didn’t just stick to something simple. So, is there an even simpler way multiply those two numbers together and then sum it while grouping everything by clientID? I’ve heard that this professor is not super helpful (I had another one) so I want to make sure to give them the best answer I can for that is only as complicated as where they are right now in the learning process.
Advertisement
Answer
SELECT clientID
,SUM(shares * price) as total
FROM table
GROUP BY clientID