SQL counting attributes of an item-CodePudding

I am new to SQL but learning more every day! These are columns in the same table I am having trouble with this particular logic:

  |Products|Ingredients|
      cake       milk
      cookie     eggs
      cake       eggs
      brownie    sugar
      cake       sugar
      cookie     sugar
      brownie    eggs
      cake       milk
      cookie     sugar
      brownie    sugar

I am looking to count how many times each ingredient is associated with each product

cake     milk  2
         eggs  1
         sugar 1
brownie  sugar 2
         eggs  1
cookie   eggs  1
         sugar 2

CodePudding user response：

You list all column which you want to group by in the GROUP BY clause, and you apply the aggregate function (COUNT, SUM, AVG, etc.) to the column you want as result in the SELECT clause:

   SELECT Products,
          Ingredients,
          COUNT(Ingredients) AS CountOfIngredients
     FROM Table1
 GROUP BY Products,
          Ingredients

SQL Fiddle