August 18, 2011 at 5:34 am
Hello
I'm facing a problem with the Group By clause, i have a query that have several sums, and i need to show 2 fields before the sums.
The first one it's the project name, and the second one it's the project current percentage, the sums are the different times spent in each factory sector.
The question is that if i use the with rollup it will duplicate each line of the project name because of the sum of the percentage for each project.
It's possible to define which columns should the rollup "handle"? Or i have to do this without the rollup?
SELECT DISTINCT
[Project] = CASE WHEN GROUPING(Project)=1 THEN '' TOTAL '' ELSE ISNULL(Project,''0000'') END,
[% Finished] = PRSS.ReadyPr,
[Sum Teste] = SUM(CASE WHEN PRSS.Section LIKE ''%Sect1'' THEN PRSS.Quantidade ELSE 0 END),
[.... other Sums] = ....
FROM tables....
WHERE something
GROUP BY Project,PRSS.ReadyPr
WITH ROLLUP
ORDER BY Project
Thanks
August 18, 2011 at 7:06 am
I forgot the Having clause for the groups... :Whistling:
Just need to add this:
HAVING (GROUPING(PRSS.ReadyPr)=0) OR (GROUPING(PRSS.ReadyPr)=1 AND GROUPING(Project)=1)
August 19, 2011 at 11:41 am
Now i'm stuck again....
I had to add more three columns, and now i need to make a combination of them all and i don't see an easy way to do this.
So i have 5 columns that doesn't are aggregated columns, and a rest of them are sums.
The 5 columns are:
Project, TypeOfProduct, %Finished, ExpectedTime, ExpectedQt
The %Finished the expectedTime and expectedQt i don't need the group total for them, so i need to find a way with a combination of grouping clauses in the having section to remove these rows in the result.
The normal behavior, i'll have something like this
Project
Project TypeOfProduct
Project TypeOfProduct %Finished
Project TypeOfProduct %Finished ExpectedTime
Project TypeOfProduct %Finished ExpectedTime ExpectedQt
What i need
Project
Project TypeOfProduct
Project TypeOfProduct %Finished ExpectedTime ExpectedQt
Thanks
August 22, 2011 at 7:26 am
Anyone? :ermm:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply