Adding Total column and displaying data per row.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Adding Total column and displaying data per row.

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-05-02 : 11:00:03

I am trying to see if I can add Total column at the end of this query, summing all items from all months per row item. I don't need a total in a footer.

create PROCEDURE [dbo].[sp_test2]

AS
BEGIN

SELECT

i_LEVEL,
mgr,
s_LEVEL,
ISNULL([1], 0) AS Jan,
ISNULL([2], 0) AS Feb,
ISNULL([3], 0) AS Mar,
ISNULL([4], 0) AS Apr,
ISNULL([5], 0) AS May,
ISNULL([6], 0) AS Jun,
ISNULL([7], 0) AS Jul,
ISNULL(

, 0) AS Aug,
ISNULL([9], 0) AS Sep,
ISNULL([10], 0) AS Oct,
ISNULL([11], 0) AS Nov,
ISNULL([12], 0) AS Dec,
ISNULL([13], 0) AS Total

FROM (
SELECT
i_LEVEL,
mgr,
s_LEVEL,
DATEPART(M, OPEN_TIME) AS AcqMonth,

i_LEVEL AS IMPACT,
noItem

FROM dbo.test_YTD

WHERE i_LEVEL like 'P1%' AND S_CODE LIKE 'P1%'
group by i_LEVEL, mgr,s_LEVEL, noItem,DATEPART(M, OPEN_TIME)

)as p
PIVOT (
COUNT(noItem) for AcqMonth in ([1], [2], [3], [4], [5], [6], [7],

, [9], [10], [11], [12], [13])
)as pvt
END

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-05-02 : 15:37:06

just join to a total table.


SELECT

i_LEVEL,
mgr,
s_LEVEL,
ISNULL([1], 0) AS Jan,
ISNULL([2], 0) AS Feb,
ISNULL([3], 0) AS Mar,
ISNULL([4], 0) AS Apr,
ISNULL([5], 0) AS May,
ISNULL([6], 0) AS Jun,
ISNULL([7], 0) AS Jul,
ISNULL(, 0) AS Aug,
ISNULL([9], 0) AS Sep,
ISNULL([10], 0) AS Oct,
ISNULL([11], 0) AS Nov,
ISNULL([12], 0) AS Dec,
isnull(b.total, 0) AS Total
from
...pvt
inner join
(
select i_LEVEL, mgr,s_LEVEL, noItem,count(noitem) as total
from 
FROM dbo.test_YTD
WHERE i_LEVEL like 'P1%' AND S_CODE LIKE 'P1%'
group by i_LEVEL, mgr,s_LEVEL
) b
on pvt.i_level = b.i_level
and a.mgr = b.mgr
and a.S_level = b.s_level
and a.noitem = b.noitem

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-02 : 19:28:47

see

http://visakhm.blogspot.com/2012/04/display-total-rows-with-pivotting-in-t.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-05-03 : 10:24:06

quote:

Originally posted by Vinnie881

just join to a total table.


SELECT

i_LEVEL,
mgr,
s_LEVEL,
ISNULL([1], 0) AS Jan,
ISNULL([2], 0) AS Feb,
ISNULL([3], 0) AS Mar,
ISNULL([4], 0) AS Apr,
ISNULL([5], 0) AS May,
ISNULL([6], 0) AS Jun,
ISNULL([7], 0) AS Jul,
ISNULL(, 0) AS Aug,
ISNULL([9], 0) AS Sep,
ISNULL([10], 0) AS Oct,
ISNULL([11], 0) AS Nov,
ISNULL([12], 0) AS Dec,
isnull(b.total, 0) AS Total
from
...pvt
inner join
(
select i_LEVEL, mgr,s_LEVEL, noItem,count(noitem) as total
from 
FROM dbo.test_YTD
WHERE i_LEVEL like 'P1%' AND S_CODE LIKE 'P1%'
group by i_LEVEL, mgr,s_LEVEL
) b
on pvt.i_level = b.i_level
and a.mgr = b.mgr
and a.S_level = b.s_level
and a.noitem = b.noitem

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

This gives me Totals column showing 1 count per row. I need to sum it up. I have a solution with a temp table but was looking for something better.
DECLARE @TempTable TABLE (
I_LEVEL varchar(5),
mgr varchar (255),
S_LEVEL varchar (255),
Jan int,
Feb int,
Mar int,
Apr int,
May int,
Jun int,
Jul int,
Aug int,
Sep int,
Oct int,
Nov int,
[Dec] int,
total int
)

update @TempTable set total = Jan + Feb + Mar + Apr + May + Jun + Jul + Aug +
Sep + Oct + Nov + [Dec]

select * from @LOBTable

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-05-03 : 14:10:39

Add noitem to the group by. If you still have an issue just provide sample data. The premise is pretty simple you are basically just generating the totals in a separate table and joining to it.

You can also just use your original query and do this


select *, Jan + feb + mar + apr + may + jun + jul + aug + sep + oct + nov +dec as Total
from
(
SELECT
i_LEVEL,
mgr,
s_LEVEL,
ISNULL([1], 0) AS Jan,
ISNULL([2], 0) AS Feb,
ISNULL([3], 0) AS Mar,
ISNULL([4], 0) AS Apr,
ISNULL([5], 0) AS May,
ISNULL([6], 0) AS Jun,
ISNULL([7], 0) AS Jul,
ISNULL(, 0) AS Aug,
ISNULL([9], 0) AS Sep,
ISNULL([10], 0) AS Oct,
ISNULL([11], 0) AS Nov,
ISNULL([12], 0) AS Dec,
ISNULL([13], 0) AS Total

FROM (
SELECT
i_LEVEL,
mgr,
s_LEVEL,
DATEPART(M, OPEN_TIME) AS AcqMonth,

i_LEVEL AS IMPACT,
noItem

FROM dbo.test_YTD


WHERE i_LEVEL like 'P1%' AND S_CODE LIKE 'P1%'
group by i_LEVEL, mgr,s_LEVEL, noItem,DATEPART(M, OPEN_TIME)

)as p
PIVOT (
COUNT(noItem) for AcqMonth in ([1], [2], [3], [4], [5], [6], [7], , [9], [10], [11], [12], [13])
)as pvt
) aaa

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources