SQL aggregate function on result of another aggregate function

Question

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.

Next time you better ask a question rather than tell a story — Alexey S. Larionov
– Alexey S. Larionov, Commented Jul 20, 2020 at 21:23

cpalmer · Accepted Answer · 2020-07-20 21:24:08Z

4

SELECT clientID
    ,SUM(shares * price) as total
FROM table
GROUP BY clientID

answered Jul 20, 2020 at 21:24

cpalmer

3112 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

holiday hawk Over a year ago

I was really over-thinking this that it would group shares and price before doing the math on them. Seeing it like this, it really is that simple, though. Thank you!

My Koryto · Accepted Answer · 2020-07-20 21:26:19Z

3

I might have missed what holdingID is necessary for in the situation you describe, but the solution seems pretty simple:

SELECT clientID, SUM(shares * price) as total
FROM table
GROUP BY clientID;

answered Jul 20, 2020 at 21:26

My Koryto

6771 gold badge4 silver badges18 bronze badges

1 Comment

holiday hawk Over a year ago

I was really over-thinking this that it would group shares and price before doing the math on them. Seeing it like this, it really is that simple, though. Thank you!

Collectives™ on Stack Overflow

SQL aggregate function on result of another aggregate function

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related