How to dynamically choose whether to SUM() column or GROU BY this column?

Hi there
I'm developping an application which would deal with costs in
organization which structure is split hierarchically to four division
levels.
eg. one can have atomic unit by setting values:
division1=A3
division2=111
division3=245
division4=1234
despite their hierarchical relation, my client wants to have reports on
all of those division combinations. so the perfect solution would be
the ultraGeneric(div1,div2,div3,div4) procedure which could do:
when called with all 4 parameters
select div1,div2,div3,div4,SUM(cost)
where div1=@.div1 and div2=@.div2 and div3=@.div3 and div4=@.div4
group by div1,div2,div3,div4
while when called with only div1 set (rest would be NULL):
select div1,'all','all,'all',SUM(cost)
where div1=@.div1
group by div1
I know how I can deal with WHERE part:
WHERE (@.DIV1 IS NULL OR @.DIV1= '' OR DIV1=@.DIV1)
but I have no idea what to do with SUM / GROUP BY part.
I can use CASE in select to choose whether to put column name or some
fake string if the column parameter is null.
but what about GROUP BY? does it behave like ORDER BY, in which I'd
have to use CASE with all combinations of parameter states?
thanks a lot
HPsorry, the post title doesn't make sense. to restate the question:
How to choose whether or not GROUP by a given column depending on
whether its value in parameter is null or not?|||You should simply GROUP BY all columns, and if there is a single value
in one of those columns (due to it being used in a condition), so be
it.
You can also use dynamic SQL. Read the following article by Erland
Sommarskog, for more approaches to the dynamic search conditions
problem:
http://www.sommarskog.se/dyn-search.html
Razvan|||Razvan Socol wrote:
> You should simply GROUP BY all columns, and if there is a single value
> in one of those columns (due to it being used in a condition), so be
> it.
hey, you're right! so case in Select will do - if I don't want to group
by a column a can just put static text instead of it.
> You can also use dynamic SQL. Read the following article by Erland
> Sommarskog, for more approaches to the dynamic search conditions
> problem:
> http://www.sommarskog.se/dyn-search.html
thanks, he saved my life once (great article about passing arrays to
procs). but thanks to your observation I won't have to use EXEC().
thanks
HP|||> > You should simply GROUP BY all columns, and if there is a single value
> > in one of those columns (due to it being used in a condition), so be
> > it.
> hey, you're right! so case in Select will do - if I don't want to group
> by a column a can just put static text instead of it.
I guess it won't do because even if I put static text into div1 column
in a select, 'group by div1' will group by original values in a
database div1 column anyway.
greetings
HP|||I read your original message again and I think I now understand what
you mean. However, the queries that you wrote there will never return
more than one row. Is this really your requirement ?
For another approach, take a look at the WITH CUBE option (see
"Sumarizing Data" in Books Online). For example:
SELECT div1,div2,div3,div4,SUM(cost)
FROM YourTable
GROUP BY div1,div2,div3,div4
WITH CUBE
I think it may return all the data you need with a single query.
Razvan