Summing Totals in Query (1 Viewer)

D

DerekDaNoob

Registered User.

Local time

Today, 17:38

Joined

Dec 16, 2014

Messages

22

• Jul 19, 2017

• #1

I am having trouble summing totals on a query

I need to group by IngredientID and have the total for that ingredient added up. Then sorted highest to lowest

 

Attachments

• 
  IMAG1113.jpg

  98.7 KB · Views: 76

Ranman256

Well-known member

Local time

Today, 03:38

Joined

Apr 9, 2015

Messages

4,337

• Jul 19, 2017

• #2

Show us the SQL
You have something in the query that is wrong.

 

D

DerekDaNoob

Registered User.

Local time

Today, 17:38

Joined

Dec 16, 2014

Messages

22

• Jul 19, 2017

• #3

Thanks for the reply

Code:Copy to clipboard

SELECT [Meal - Recipes IngredientListQ].MealID, [Meal - Recipes IngredientListQ].IngredientID, [Meal - Recipes IngredientListQ].IngredientName, Sum([Meal - Recipes IngredientListQ].Qty) AS SumOfQty
FROM [Meal - Recipes IngredientListQ]
GROUP BY [Meal - Recipes IngredientListQ].MealID, [Meal - Recipes IngredientListQ].IngredientID, [Meal - Recipes IngredientListQ].IngredientName, [Meal - Recipes IngredientListQ].ID
ORDER BY [Meal - Recipes IngredientListQ].IngredientName, Sum([Meal - Recipes IngredientListQ].Qty) DESC;

 

P

plog

Banishment Pending

Local time

Today, 02:38

Joined

May 11, 2011

Messages

11,646

• Jul 19, 2017

• #4

Demonstrate your issue with data please. Provide 2 sets of data:

A: starting data from your table(s). Include table and field names and enough data to cover all cases.

B. expected results from A. Show what you expect to end up with when you feed in the data from A.

 

Ranman256

Well-known member

Local time

Today, 03:38

Joined

Apr 9, 2015

Messages

4,337

• Jul 19, 2017

• #5

Remove the
GROUP BY [Meal - Recipes IngredientListQ].ID

you dont want to group this. ID shouldnt be in the query.

 

A

almahmood

Registered User.

Local time

Today, 13:08

Joined

Mar 28, 2017

Messages

47

• Jul 19, 2017

• #6

Try this:

SELECT [Meal - Recipes IngredientListQ].MealID, [Meal - Recipes IngredientListQ].IngredientID, [Meal - Recipes IngredientListQ].IngredientName, Sum([Meal - Recipes IngredientListQ].Qty) AS SumOfQty
FROM [Meal - Recipes IngredientListQ]
GROUP BY [Meal - Recipes IngredientListQ].MealID, [Meal - Recipes IngredientListQ].IngredientID, [Meal - Recipes IngredientListQ].IngredientName
ORDER BY [Meal - Recipes IngredientListQ].IngredientName, Sum([Meal - Recipes IngredientListQ].Qty) DESC;