How would I make my SQL statement show only the first line (lowest price + total purchases)

Question

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.sstatic.net/5biiM.png

And this is what it is: https://i.sstatic.net/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

Answer 1

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.