0

I'm having a problem doing a mysql query.

I have two tables.

create table product (
productid       integer,
productname     varchar(20),
cost            float,
primary key(productid)
)engine=innodb;

create table purchases (
purchaseid      integer,
fproductid      integer,
total           integer,
primary key(purchaseid, fproductid),
foreign key(fproductid) references product(productid)
)engine=innodb;

What I want to get from these two tables is the top 10 most popular products purchased, and I want the product name, not the productid. So basically the names of the product id's that appears the most times in the 'purchases' table and how many times this product has been purchased.

select max(productname), productname 
from product, purchases 
where product.productid=purchase.fproductid 
group by productid 
limit 10;

This is what I've tried so far, but I'm not getting the correct values.

Thank you.

leo
  • 91
  • 1
  • 10
  • 1
    Hint: Use `JOIN`, `SUM(total)`, `GROUP BY productid`, `ORDER BY`, and `LIMIT 10`. – Barmar Oct 04 '21 at 20:00
  • @Barmar Sorry about that. I'm trying to atleast figure out how to get the count for each product to start with. But I only get a sum number of all the products that exist in the tables. I've updated the post. – leo Oct 04 '21 at 20:08
  • You're missing both `GROUP BY` and `ORDER BY`. – Barmar Oct 04 '21 at 20:10
  • Cross Apply/Latteral? https://stackoverflow.com/questions/36869221/cross-outer-apply-in-mysql – xQbert Oct 04 '21 at 20:14

1 Answers1

1

You need to use GROUP BY product.productid to get a row for each product, rather than a sum of everything.

Use ORDER BY total_purchases DESC to get the top purchases.

SELECT productname, COUNT(*) AS total_purchases
FROM product
JOIN purchases ON product.productid = purchases.fproductid
GROUP BY product.productid
ORDER BY total_purchases DESC
LIMIT 10
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • ok will try. This actually worked though: select max(productname), productname from product, purchases where product.productid=purchase.fproductid group by productid order by productname limit 10; – leo Oct 04 '21 at 20:17
  • the total variable in my purchases table doesn't refer to how many products ordered but the total cost of the purchase. I was pretty unclear on that, my bad. But the problem is solved now. Thanks alot for taking your time! – leo Oct 04 '21 at 20:21
  • OK, then change `SUM(total)` to `COUNT(*)`. – Barmar Oct 04 '21 at 20:22
  • I did, thanks alot! @barmar – leo Oct 04 '21 at 21:13