CODEWITHSUNDEEP
sqldatabasesqlite
Kenkuts
Kenkuts

Reputation: 69

How to add multiple conditions in a sql query with aggregate function?

I am creating a join table where I am getting the name of a store that sold the most iphones. My query looks like as follows:

SELECT stores.names, MAX(products.name) from stores
JOIN products on products.id = stores.items_sold
WHERE products.name = "iPhone";

Basically I am getting the MAX number of products that is iphone that got sold the most to which store. The query is only supposed to give me back the store name and that's it. This one gives me the correct answer but it prints out the product in a MAX column. I only need the store.name.

I also tried:

SELECT stores.names from stores
JOIN products on products.id = stores.sold_id
WHERE products.name = "iPhone"
GROUP BY store.names
HAVING MAX(products.name);

I just want to be able to put two conditionals for my query. It just needs to be iphone and which store sold the most.

Upvotes: 0

Views: 315

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

You were just there , just need to add aggregate function and limit clause

   SELECT stores.names from stores
   JOIN products ON products.id = stores.items_sold
   WHERE products.name = "iPhone"
   GROUP BY stores.names
   ORDER BY count(stores.items_sold) desc
   LIMIT 1;

Upvotes: 1

auro
auro

Reputation: 27

--You can also write like this:

using with clause

with aa as
(SELECT MAX(p.name) maxname from stores s
JOIN products p on p.id = s.items_sold
WHERE p.name = 'iPhone')
select s.name,aa.maxname 
from stores s,products p,aa 
where p.name=aa.maxname

Upvotes: 0

Related Questions