Others may disagree(and probably will...) but I do suggest that you put your aggregate queries in stored procedures if they're not changing often as this will give you a further speed bump. Obviously, if you need to create the query dynamically because your fields change, stored procedures may lead to duplicate code. There are ways around this, too, but it gets then harder to maintain and you will prefer sticking to simple things and keep it out of a stored in those cases.
A On Feb 6, 2013 6:56 PM, "Woody" <[email protected]> wrote: > > I am fairly new at using Firebird SQL and I would like some direction on > > how to include some calculations into my code. > > > > For example I want to read a table, group by a part number and then get a > > total price for all the items in the group. Would this be something for a > > SP? > > > > I have looked online but couldn't find a complete answer. > > > > Thank you for any help/direction that you can give on this. > > > > You use what is known as an aggregate query. Aggregate queries are ones > where you use individual columns for the grouping and calculations for > those > groups. Something along the lines of: > > Select PartNumber, sum(PartCost * PartQty) from Parts > group by PartNumber > > This would return a list of distinct part numbers along with their total > costs based on the individual cost times the number of parts. For example, > suppose you have these in the parts table: > > PART COST QTY > Widget 1 1.50 3 > Widget 1 1.45 5 > Widget 2 2.20 1 > Widget 2 2.00 3 > > The result would be: > Widget 1 11.75 > Widget 2 8.20 > > > For more complex calculations, it might be necessary to create a stored > procedure but in most cases, queries should do what you want. > > HTH > Woody (TMW) > > > > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > > Also search the knowledgebases at http://www.ibphoenix.com > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Yahoo! Groups Links > > > > [Non-text portions of this message have been removed]
