SQL Test

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.

  unitsonorder * (unitprice - unitcost)

Seems simple enough…

SELECT companyname, unitsonorder * (unitprice - unitcost)
FROM northwinds.products
JOIN northwinds.suppliers ON products.supplierid = suppliers.supplierid;

Company	Current Item Profit
Exotic Liquids	0
Exotic Liquids	126.8
Exotic Liquids	39.9

Note: I'm only showing the results for the first company to reduce table size.

Ah okay now we need to get the sum of the units by each company…

SELECT companyname, unitsonorder * (unitprice - unitcost)
FROM northwinds.products
JOIN northwinds.suppliers ON products.supplierid = suppliers.supplierid
GROUP BY companyname;

Error: “not a GROUP BY expression”

Well shit.
After trying a bunch of things, I finally got something to work.

SELECT companyname, SUM(currentordertotal) AS ORDERPROFIT
FROM (
  SELECT companyname, (unitsonorder * (unitprice - unitcost))
  AS CURRENTORDERTOTAL FROM northwinds.products
  JOIN northwinds.suppliers
  ON products.supplierid = suppliers.supplierid
  )
GROUP BY companyname;

Company	Current Order Profit
Exotic Liquids	166.7

Note: I'm only showing the results for one company to reduce table size.

And while that awkward nesting works… after a little more tinkering I realized this is what it really should be.

SELECT companyname, SUM(unitsonorder * (unitprice - unitcost))
FROM northwinds.products
JOIN northwinds.suppliers
ON products.supplierid = suppliers.supplierid
GROUP BY companyname;

Much cleaner to read and execute.
Now we can sort the output by adding this:

SELECT companyname, SUM(unitsonorder * (unitprice - unitcost))
FROM northwinds.products
JOIN northwinds.suppliers
ON products.supplierid = suppliers.supplierid
GROUP BY companyname
ORDER BY SUM(unitsonorder * (unitprice - unitcost)) DESC;

Now let's clean up the column names...

SELECT companyname, SUM(unitsonorder * (unitprice - unitcost))
AS ORDERPROFIT
FROM northwinds.products
JOIN northwinds.suppliers
ON products.supplierid = suppliers.supplierid
GROUP BY companyname ORDER BY orderprofit DESC;

And that's it! Successfully calculated all supplier current order profits.
You can paste the above code into FSBoracle and see the values.