SQL – Finding Percent Of a Total and subtracting to get two new totals

In my SQL course I’m trying to answer a problem in which i’m being asked to find X% of a Total then subtracting that result from the original total to produce another result. Then putting these results (the X% of the total and the total-X% of total) in two new columns.

Example: We need to know how much money we owe Tom and Ted. We have total up sales to $1,000,000.00. We owe Tom 75% of that total. The remainder goes to Ted.

I can’t seem to find anything in my readings/videos about this nor a google search that isn’t an answers that produces ratios or comparing to other records in the table. Also, not sure about how to get the results into their own columns. Thanks for any advice!

Example of what I got so far:

SELECT SUM(Sale_Amount) From Order_Table

Now I have to find the % of that SUM then subtract it from the SUM and push both results to two new columns, one for the percent of the SUM(Sale_Amount) and one for the remainder.

Answer

Given it’s an SQL course (and it’s not 100% clear what’s being asked), I’m not going to give you the total answer, but I’ll give you components but you’ll need to understand them to put them together.

In SQL, you can

• Get totals using SUM and GROUP BY
• Do normal maths e.g., SELECT 10000 * 60/100 to get percentages of totals
• ‘Save’ results by a) having columns/fields to save them in, and b) UPDATE those fields with relevant data

Note if you’re not saving data, and simply reporting them, you can just add those to a SELECT statement e.g., SELECT 100000 AS Total, 100000 * 0.75 AS Toms_Share, 100000 * 0.25 AS Teds_Share.