I am not sure how to describe this and I'm sorry if the subject isn't very clear.
Here is what I want to do: I have to generate reports for my employer and sometimes he wants to see things at varying degrees of detail. For example:
(The example will have a Table called "Cars" with columns "Yr", "Make", "Model", and "Price")
Sometimes I need to know, say...the average price of all cars for a year (this query):
SELECT Yr, AVG(Price)
GROUP BY Yr
And Sometimes I need the average price for all cars of a year and make (this query):
SELECT Yr, Make, AVG(Price)
GROUP BY Yr, Make
Is there any way using just SQL to write a query that could handle both cases? I am integrating this within a .NET application so of course I could always build up the query string in my C# file depending on the requirements, but that feels so messy and is a real pain to debug, especially when you start adding rollups and whatnot. Also it seems that doing as much in SQL and as little in .NET is always best for performance.
I've thought of is to write a procedure for each "level" of detail, but the amount of redundancy within those procedures would be really high, and the last thing I want to do is update 4 procedures when I need to make a change.
I've also thought of writing a query that returns all of the data at every level and then just pick what is needed out of that, but that takes too much time.
If you have any suggestions, throw them my way! Thanks in advance for any help,