combining Totals in MSSQL

[mfilpo]

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.

[code]$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;";[/code]

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.

[GeoffOs]

You can use the group by clause. So your SQL would look something like this:
[code]
$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;";
[/code]

(I think)

[mfilpo]

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 !

[code]
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";
[/code]

Thanks for all your help

[MCP]

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!