I've got this SQL exercise I'm trying to complete but I can't figure out how to specifically show one row that should contain the media type with the lowest # of purchases and purchase value.
I'm completely unsure of what I should add after my 'group by'?
So this is what I want my output to be: https://i.stack.imgur.com/5biiM.png
And this is what it is: https://i.stack.imgur.com/oGyLV.png
Note that I'm using SQLite
SELECT
M.name AS'MediaType Name',
(SELECT MIN(I.unitprice * I.quantity) FROM tracks) AS 'Minimum Total Purchase Value',
(SELECT COUNT(I.Quantity) FROM invoice_items) AS 'Total Number of Purchase'
FROM
media_types M
JOIN
tracks T
JOIN
invoice_items I ON M.MediaTypeId = T.MediaTypeId
AND T.trackid = I.trackid
--This is where I think I'm making a mistake:
GROUP BY
M.name
CodePudding user response:
Join the tables properly with each ON
clause after its respective join and aggregate.
Sort the results by Total Number of Purchase
ascending and return the top row:
SELECT M.name AS `MediaType Name`,
MIN(I.unitprice * I.quantity) AS `Minimum Total Purchase Value`,
COUNT(*) AS `Total Number of Purchase`
FROM media_types M
JOIN tracks T ON M.MediaTypeId = T.MediaTypeId
JOIN invoice_items I ON T.trackid = I.trackid
GROUP BY M.name
ORDER BY `Total Number of Purchase` LIMIT 1;
Depending on the requirement, you may also use Minimum Total Purchase Value
in the ORDER BY
clause:
ORDER BY `Total Number of Purchase`, `Minimum Total Purchase Value` LIMIT 1
Never use single quotes for table/column aliases.
For SQLite you can use double quotes, backticks or square brackets.