What SQL query should I use?

Question

This the table. The question is *Find the pizza which uses the largest number of ingredients*

I know the answer is special but I don't know how find it out using a query. I have tried many different ways, which didn't work. Thanks in advance.

   pizza    | ingredient | amount 
------------+------------+--------
 margarita  | cheese     |    120
 margarita  | spice      |      5
 ham        | ham        |    150
 ham        | spice      |      5
 napolitana | anchovies  |    100
 napolitana | olives     |     75
 napolitana | spice      |     10
 hawaiian   | ham        |    100
 hawaiian   | pineapple  |    100
 hawaiian   | spice      |      5
 cabanossi  | cabanossi  |    150
 cabanossi  | spice      |     10
 siciliano  | onion      |     50
 siciliano  | capsicum   |     75
 siciliano  | olives     |     50
 siciliano  | anchovies  |     50
 siciliano  | spice      |     15
 americano  | salami     |    120
 americano  | pepperoni  |     75
 americano  | spice      |     10
 mexicano   | onion      |     75
 mexicano   | capsicum   |     75
 mexicano   | mushroom   |     50
 mexicano   | chilli     |     25
 mexicano   | spice      |     20
 seafood    | seafood    |    200
 seafood    | spice      |      5
 garlic     | garlic     |     25
 garlic     | spice      |     10
 vegetarian | onion      |     50
 vegetarian | capsicum   |     50
 vegetarian | mushroom   |     50
 vegetarian | peas       |     50
 vegetarian | tomato     |     50
 vegetarian | spice      |      5
 mushroom   | mushroom   |    100
 mushroom   | spice      |      5
 special    | cheese     |     25
 special    | tomato     |     25
 special    | ham        |     25
 special    | anchovies  |     25
 special    | olives     |     25
 special    | mushroom   |     25
 special    | bacon      |     25
 special    | egg        |     25
 special    | pineapple  |     25
 special    | cabanossi  |     25
 special    | salami     |     25
 special    | capsicum   |     25
 special    | onion      |     25
 special    | peas       |     25
 special    | seafood    |     25
 special    | spice      |     10
 stagiony   | ham        |     75
 stagiony   | mushroom   |     50
 stagiony   | olives     |     50
 stagiony   | anchovies  |     25
 stagiony   | spice      |     10

---

I just needed to know the name of pizza which has used most ingredient. And the answer is Select pizza from recipe group by pizza having count()>=all (select count() from recipe group by pizza);

and the output is:

pizza

special (1 row)

Answer 1

...largest number of ingredients

The following query lists all the pizza with their total number of ingredients. Add LIMIT 1 if you want to show the pizza with the largest number of ingredients.

SELECT pizza, COUNT(*) totalIngredients
FROM tableName
GROUP BY pizzaList
ORDER BY totalIngredients DESC
-- LIMIT 1

SQLFiddle Demo

A problem arises with this query, however. If two pizzas share the largest number of ingredients, only the first in the sort order would be shown. To display all pizzas in the top spot, use the code shown below:

SELECT pizza, COUNT(*) totalIngredients
FROM pizzaList
GROUP BY pizza
HAVING COUNT(*) = 
(
  SELECT MAX(totalCount)
  FROM
  (
    SELECT  COUNT(*) totalCount
    FROM    pizzaList
    GROUP BY pizza
  ) x
)

SQLFiddle Demo

Answer 2

The answer to your question depends on a few things. Do you want the largest amount of ingredients overall, or the greatest count of different ingredients. Also, do you only want the first, or a list in order?

First, let's assume you want the overall amount of toppings. The query for this is as follows:

SELECT TOP (1) Pizza, sum(amount) as TotalAmount
FROM PizzaTable
GROUP BY Pizza
ORDER BY sum(amount) DESC

To get more than just the very top row, remove TOP (1).

Now, let's assume you want the count of ingredients, rather than the total amount. The query for this would be:

SELECT TOP (1) Pizza, count(*) as NumberOfIngredients
FROM PizzaTable
GROUP BY Pizza
ORDER BY count(*) DESC

Again, remove TOP (1) for a list rather than a single entry.

Answer 3

Assuming amount is the amount of that ingredient and you mean the largest number (i.e. quantity) of ingredients:

SELECT pizza
FROM tableName
GROUP BY pizza
ORDER BY SUM(amount) DESC
LIMIT 1

If you instead mean the largest number of different ingredients (i.e. the count of unique ingredients):

SELECT pizza
FROM tableName
GROUP BY pizza
ORDER BY COUNT(ingredient) DESC
LIMIT 1

Note, you may need to add a DISTINCT to the count if it's possible to have the same ingredient listed twice for a given pizza.

Answer 4

It would be:

SELECT pizza, COUNT(pizza) AS ingredientcount FROM pizzas GROUP BY pizza ORDER BY ingredientcount DESC LIMIT 1;