How to run query, sum the columns, transfer to a table?

Hi everyone! I am a complete SQL noob and I've been trying to train myself online but I can't even get this query to work. I'm not even sure if it's possible or if my question makes sense but I'll try and explain it and any help at all is appreciated!

Problem: I have a master table with many different levels (categories). I'm trying to write sql code that will ask the user for which level they want to see, then when the data is filtered by the query, the actual numbers can be added up by column to see the subtotals along with the final total.

To make this more clear I've included an example of what the table looks like and what I need the SQL to do:

1. Ask the user to enter which levels to filter by:
Level 1: Fruit
Level 2: Sweet
Level 3: * (all values)

2. After filtering using the SQL query Tim and Sonny's data should be left. Now i need to sum up their respective revenues and put them in a new row at the bottom.

I hope this makes sense!! Its a very simplified version but its essentially the same, i'm sure i'll figure out the logic if you can run it for this sample. Thanks!! :clap:

You can goto Data-->Filter-->AutoFIlter. Then use the dropdowns to select your criteria. After the criteria is selected, goto Data-->Subtotals and add checkmarks to Apple Revenue, Orange Revenue, and Lime Revenue. When done viewing the subtaotals goto Data-->Subtotals and select "Remove All".

If you are dead-set on a VBA method you can automate turning on and off the autofilter as well as adding the sub-totals. I've controlled the autofilter from VBA before but I've never turned on/off subtotals.

Have you considered using the SQL query as data for a Pivot Table - where totals/subtotals are displayed. .02 Stan

Quote:

---

Originally Posted by stanl

Have you considered using the SQL query as data for a Pivot Table - where totals/subtotals are displayed. .02 Stan

---

Yeah, if you are looking for final ouput that is interactive with the user, then pivot table is the only way to go.

Better to just present ALL the data in an interactive format, then to get variables from the user to customize the SQL string.

Quote:

---

Originally Posted by stanl

Have you considered using the SQL query as data for a Pivot Table - where totals/subtotals are displayed. .02 Stan

---

Yeah, if you are looking for final ouput that is interactive with the user, then pivot table is the only way to go.

Better to just present ALL the data in an interactive format, then to get variables from the user to customize the SQL string.

EDIT: here is a really good book on SQL basics.

http://www.amazon.com/Sams-Teach-You...dp/0672325675/