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

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.