August 9, 2007 at 11:53 am
Greetings,
I haven't found a particular example but I was wondering if it was possible to get a 'grand total' column factored into the result set of a cte/pivot query?
My resultset has a veritical column of descriptions and a horizontal column of gradings.
TYPE AAA AA A BBB OTHER
Corp 0.78 0.28 1.55 1.06 NULL
ExMuni 48.66 9.17 0.76 0.41 NULL
Is there any way I could get a summation of the gradings as part of the CTE/pivot?
I realize I could probably stuff it all into a declared table and then get the totals that way but thought I would check.
thanks,
Chris
August 9, 2007 at 2:09 pm
What is the expected output?
N 56°04'39.16"
E 12°55'05.25"
August 9, 2007 at 4:01 pm
SELECT
Type, AAA, A, BBB, OTHER, (AAA + A + BBB + OTHER) AS Total
FROM (...)
-Eddie
Eddie Wuerch
MCM: SQL
October 9, 2007 at 9:34 am
WITH CATEGORY(TYPE_1, RATING, BVALUE) AS
(
SELECT TYPE_1
,CASE RATING
WHEN 'AAA' THEN 'AAA'
WHEN 'AA' THEN 'AA'
WHEN 'AA+' THEN 'AA'
WHEN 'AA-' THEN 'AA'
WHEN 'A' THEN 'A'
WHEN 'A+' THEN 'A'
WHEN 'A+ /*-' THEN 'A'
WHEN 'A-' THEN 'A'
WHEN 'BBB' THEN 'BBB'
WHEN 'BBB+' THEN 'BBB'
WHEN 'BBB-' THEN 'BBB'
ELSE 'OTHER'
END
,(BVALUE/@intTotalValue)*100
FROM View_DATA AS b
)
SELECT * FROM CATEGORY
PIVOT
(
SUM(BVALUE)
FOR RATING
IN (AAA, AA, A, BBB, OTHER)
)
AS P
ORDER BY TYPE_1
The pivot gives the totals for each TYPE_1 (corp,treasury,muni,etc,etc)
basically what I am looking for a 'grand total' for each AAA,AA,A,BBB, and OTHER column across the bottom. If I could figure out how to get it across for each TYPE_1 that would be amazing, but I'm already out in deep water here.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply