0

So I need to count the number of associated entries in a relational database without using an aggregate function. Out current database represents recipes and their ingredients. I need something along the lines of: 

SELECT `RecipeId`,`RecipeTitle`, COUNT(`IngredientId`) AS `IngredientCount` 
FROM `recipes` Natural Join `recipe_ingredients` 
GROUP BY `RecipeID` 
HAVING `IngredientCount` < 5

But without using COUNT().

Thank you.

Index out of bounds
  • 33
  • 4
  • 1
    How would one get a count without counting? "I have some number of items in this paper bag. Without counting them tell me how many are in there". – JNevill Apr 20 '17 at 19:47
  • 1
    out of curiosity, why are you avoiding aggregate functions? they are probably the most efficient way to do this – Ananth Rao Apr 20 '17 at 19:47
  • only without COUNT or any other aggregated function can't be used? – McNets Apr 20 '17 at 19:53
  • Ok! Simulate Row_Number() over (partition by RecipieID, IngredientID). then only pull back recipes which do not have a row_number greater than 5. Simulation example [here](http://stackoverflow.com/questions/11963818/row-number-equivalent-in-mysql-for-inserting). or maybe... https://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/ – xQbert Apr 20 '17 at 19:53
  • Loop over the results and increment your own counter. But.. Just use aggregate functions. – ScottSmudger Apr 20 '17 at 20:16
  • Sorry, should have been more clear. This is a SQL programming assignment, and this is the only one I couldn't figure out. He hasn't really gone over why we would ever do this, so it's an odd problem. – Index out of bounds Apr 21 '17 at 01:52

1 Answers1

1

Try with this:

I don't know IngredientId data type, if it is a text field change @LastId = 0 by @LastId = ''

SELECT `RecipeId`, `RecipeTitle`, CountIngredient
FROM ( 
      SELECT `RecipeId`, `RecipeTitle`, 
             if(@LastId <> `IngredientId`, @Count := 1, @Count := @Count + 1) CountIngredient, 
             @LastId := `IngredientId`
       FROM 
             (select @Count := 0, @LastId := 0) x,
             (SELECT `RecipeId`, `RecipeTitle` 
              FROM `recipes` Natural Join `recipe_ingredients`
              ORDER BY `RecipeId`) y
    ) z
WHERE z.CountIngredient < 5;
McNets
  • 10,352
  • 3
  • 32
  • 61