April 1, 2008 at 11:50 am
I have the following 5 columns in a report
Byteid, Variance, Distribution, Total, Tor DiffPercent(tor/total)
111 3636 4333A 112.2 121.43
221 32323 474747QT 32.23 37.33
Now at the bottom part, I have to show the SUM(total) & SUM(Tor) and at the same time I have to calculate the DiffPercent which is tor/total*100 and display the results in the descending order of Diffpercent.
So what I did is the following
select Byteid, Variance, Distribution, Total,Tor From TTor where variance 0
union all
select '0', '0','SUM', SUM(total), SUM(tor) from TTor
order by Diffpercent desc
So when I do the above, it is not showing me the SUM(total) & SUM(Tor) at the bottom and they are shown in the middle somewhere based on the calculated Diffpercent between SUM(total) & SUM(tor).
Please help !
April 1, 2008 at 12:06 pm
I currently am in a conference call but I almost bet you could accomplish your goal with GORUP BY WITH ROLLUP like this
SELECT
Byteid,
Variance,
Distribution,
SUM(Total) Sum_Total,
SUM(Tor) Sum_Tor
FROM
dbo.TTor
WHERE
variance = 0 -- this was missing as far as operator so I assumed =
GROUP BY
Byteid,
Variance,
Distribution
WITH ROLLUP
There is an opreator you can get your groups where it rolled up. Look at GROUPING.
April 1, 2008 at 12:21 pm
Antares686,
I think your observation is right however, I cannot cut & copy stuff from my app & it was a typo. My mistake that you didnt see = in there.
Getting back to the problem, I guess I'm bit ambiguous here. Not only do I want the data in the above format but I also want the SUM of TOR & SUM of TOTAL to show up at the bottom. So what I did was do an ADDITION OUTSIDE the query itself & then used UNION ALL to match both up. However when I calculate the value of DiffPercent & sort the results by diffpercent desc, the total at the bottom is mixing up with rows.
How do I accomplish this was my question.
Thank You.
April 1, 2008 at 12:40 pm
Right, using GROUP BY ... WITH ROLLUP you can sort with the GROUPING function like so
ORDER BY
(CASE WHEN GROUPING(Byteid) = 1 AND GROUPING(Variance) = 1 AND GROUPING(Distribution) = 1 THEN 1 ELSE 0),
Thus when it is the total summed values it drops to the bottom. You might have to tweak a hair for the sake of rollups that otherwise occurr but you can sort you items like stated above. See "GROUPING keyword" in BOL
April 1, 2008 at 3:43 pm
Antares686,
The rollup did the trick !!! Thank you very much.
As an addendum to this, how do I find the count(s)? Can it be done ?
Now, how do I sort the output on the percentage feild ? It is throwing me syntax errors when I ran what you wrote in here.
April 2, 2008 at 7:22 am
Can you post what you have so far. I do better with a starting point. 😀
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply