Jump to content


Photo

combining Totals in MSSQL


  • Please log in to reply
3 replies to this topic

#1 mfilpo

mfilpo
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 05 October 2006 - 08:27 AM

Hi all,

I apologize if this has been answered already in another post (however i did a search and couldn't find any answers).

I'm building a little project to help me learn MSSQL and PHP and I have an idea of what i want to acheive, i'm just not sure how to go about it.

The database is like a production for recipes to make each day, with tables for recipes, ingredients, recipeMakeup table (which contains the recipeID, ingredientID and the quantity of the ingredient required), production etc.

I would like to run a report for all the ingredients i will need if i want to make multiple recipes.

So far i have succesfully generated the report with a simple while loop that queries the ingredients table for the recipes i've selected.

$query = "select * from ingredients, production, recipes, recipeMakeup where production.productionDate = '$today' AND recipes.recipeID = recipeMakeup.recipeID AND recipeMakeup.ingredientID = ingredients.ingredientID AND production.recipeID = recipes.recipeID ORDER BY ingredientName ASC;";

it returns results as follows :

Ingredient Name                  Qty (kg)
============                  ======

Capsicum                             0.7
Carrot                                 0.5
Carrot                                 0.6
Carrot                                 0.2
Celery                                 0.1
Eggplant                              0.5

etc etc.

what i would like would be to have the report come back lookiing like this :

Ingredient Name                  Qty (kg)
============                  ======

Capsicum                             0.7
Carrot                                 1.4
Celery                                 0.1
Eggplant                              0.5

in other words, not displaying each ingredient at every pass. Instead, adding the total of any ingredient with more than one entry before displaying it.

I could probably acheive it at a PHP level with a few if statements, but i figured there might be a way to simply do it at a query level. this would make it more efficient.

Any help would be greatly appreciated.

Thanks guys,

Mark.

#2 GeoffOs

GeoffOs
  • Members
  • PipPip
  • Member
  • 24 posts
  • LocationCheshire, England

Posted 05 October 2006 - 07:23 PM

You can use the group by clause. So your SQL would look something like this:
$query = "select * from ingredients, production, recipes, recipeMakeup where production.productionDate = '$today'";
$query.= "AND recipes.recipeID = recipeMakeup.recipeID";
$query.= "AND recipeMakeup.ingredientID = ingredients.ingredientID";
$query.= "AND production.recipeID = recipes.recipeID ";
$query.= "GROUP BY ingredientName";
$query.= "ORDER BY ingredientName ASC;";

(I think)
Beyond a critical point within a finite space, freedom diminishes as numbers increase....[br]The human question is not how many can possibly survive within the system, but what kind of existence is possible for those who do survive."[br]-- Frank Herbert - Dune

#3 mfilpo

mfilpo
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 06 October 2006 - 08:59 AM

After a bit of messing around i couldn't figure out the GROUP BY function..

ended up trying to acheive it in PHP.

I know my techniques are a bit sloppy, but it works !

			while ($row = @mssql_fetch_array($result)) 	{
				$totaltemp = ($row[qty] / 100) * $row[totalQty];
				$total = round($totaltemp, 2); 
				
				$x++;

				if ($x == '1'){
					$currentIngredient = $row[ingredientName];
					$currentQty = $total;
				} else {
					if ($row[ingredientName] == $currentIngredient){
						$currentQty = $currentQty + $total;
					} else {		 
						echo "<tr> \n";
						echo "<td>$currentIngredient</td><td>$currentQty</td> \n";
						echo "</tr> \n";	
						$currentIngredient = $row[ingredientName];
						$currentQty = $total;
					}
				}		
			}

			echo "<tr> \n";
			echo "<td>$currentIngredient</td><td>$currentQty</td> \n";
			echo "</tr> \n";

Thanks for all your help

#4 MCP

MCP
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 15 October 2006 - 04:55 AM

Group by is the best, but whatever works i suppose. Here's the SQL:

select ingredients.name, sum(recipeMakeup.Qty)
from ingredients, production, recipes, recipeMakeup
where production.productionDate = '$today' AND recipes.recipeID = recipeMakeup.recipeID AND recipeMakeup.ingredientID = ingredients.ingredientID AND production.recipeID = recipes.recipeID
GROUP BY ingredients.name
ORDER BY ingredientName ASC;


basically, anything else that you want to select that is not aggregated (i.e. sum, avg, etc.) needs to also be in the group by statement. good luck!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users