SQLTeam.com | Weblogs | Forums

Sum a nested query

Hey Everyone,
Our accounting dept wants to add the tax collected or percentage for an item to the Consolidated Menu Item Sales Detail report. I have some experience with SQL, but it's been a long time since I used it on a regular basis and could use some help putting this query together. Below are the queries I put together to determine what tables and fields I need to work with.

select * from micros.mi_def where obj_num='9230188' - Menu item table, var will be supplied by batch process. Obj_num is menu item #

select * from micros.mi_type_class_def where mi_type_seq='116' - mi_type_seq is the menu item class number. The class controls taxes charged for that item.

select * from micros.tax_class_def where tax_class_seq='3' - tax_class is the menu item tax classification

3,102,'Wine/Liqu Tax = State+Liq','T','T','F','F','F','F','F','F','F','F','F','F','F','F','F','F',462,1997-06-16 16:06:18.437
the 'T''s about that are in bold say to charge each of those taxes defined in the tax_rate_def table

select * from micros.tax_rate_def - Table with tax rates.

Finally, sum the values that returned True. In this case, both rates in the field percentage from records that have seq 1 and 2 need to be totalled. The sum of the percentages that are marked as true is what I am trying to pull into the report.
select sum(percentage) from micros.tax_rate_def where tax_rate_seq between 1 and 2

We are using the Sybase DB that comes with Micros 5.7 build 5341. All help is appreciated. Thanks.