Getting better at SQL through trial and error
Ok so in an attempt to get better at SQL I wanted to find a result I wanted from the data and work out how to get it in SQL.
I decided to try to find out how the amount of profit each supplier is currently bringing in.
To do this I would need to find the products sold by each supplier and the profit of those item by subtracting the UNITCOST by the UNITPRICE and then multiply that by the amount of units ordered.
Seems simple enough…
Note: I'm only showing the results for the first company to reduce table size.
||Current Item Profit
Ah okay now we need to get the sum of the units by each company…
Error: “not a GROUP BY expression”
After trying a bunch of things, I finally got something to work.
Note: I'm only showing the results for one company to reduce table size.
||Current Order Profit
And while that awkward nesting works… after a little more tinkering I realized this is what it really should be.
Much cleaner to read and execute.
Now we can sort the output by adding this:
Now let's clean up the column names...
And that's it! Successfully calculated all supplier current order profits.
You can paste the above code into FSBoracle and see the values.