cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to give additional column

former_member261836
Participant
0 Kudos

Hi everyone,

can anyone give 2 additional coumns for this query like Grand total and Percentage of Grand total

SELECT p.state,

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([8],0) as [Aug],

ISNULL([9],0) as [Sep],

ISNULL([10],0) as [Oct],

ISNULL([11],0) as [Nov],

ISNULL([12],0) as [Dec],

p.[1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]

FROM

(SELECT T2.state, sum(T1.[Doctotal]) AS [BAL],MONTH(T1.Docdate) as [month]

FROM

[dbo].[OCRD]  T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode]

INNER JOIN CRD1 T2 ON T0.[CardCode] = T2.[CardCode]

Where  T0.[CardType] ='C' AND Year(T1.Docdate) = 2012

Group by

T1.DocDate,

t2.State)S

PIVOT 

(SUM(S.[BAL]) FOR [month] IN

         ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Order By

P.State,

p.[1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]

Accepted Solutions (1)

Accepted Solutions (1)

KennedyT21
Active Contributor
0 Kudos

Try This

SELECT tt.[state], tt.Jan, tt.Feb, tt.Mar, tt.Apr, tt.May, tt.Jun, tt.Jul, tt.Aug,

       tt.Sep, tt.Oct, tt.Nov, tt.Dec,  (TT.Jan+ TT.Feb+tt.Mar +tt.Jun+tt.Jul+tt.Aug+tt.Sep+tt.Oct+tt.Oct+tt.Nov+tt.Dec) AS Total

  from ( SELECT p.state,

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([8],0) as [Aug],

ISNULL([9],0) as [Sep],

ISNULL([10],0) as [Oct],

ISNULL([11],0) as [Nov],

ISNULL([12],0) as [Dec]

FROM

(SELECT T2.state, sum(T1.[Doctotal]) AS [BAL],MONTH(T1.Docdate) as [month]

FROM

[dbo].[OCRD]  T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode]

INNER JOIN CRD1 T2 ON T0.[CardCode] = T2.[CardCode]

Where  T0.[CardType] ='C' AND Year(T1.Docdate) = 2012

Group by

T1.DocDate,

t2.State)S

PIVOT

(SUM(S.[BAL]) FOR [month] IN

         ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

) AS TT

former_member261836
Participant
0 Kudos

Thank you

KennedyT21
Active Contributor
0 Kudos

If you got the solution Close the Thread...

Answers (2)

Answers (2)

former_member261836
Participant
0 Kudos

thanks for your reply but in Grand Total column is blank sir, addition of Jan to Dec columns values are not happening.

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this query for grand total. advice formula for grand total percentage:

SELECT p.state,

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([8],0) as [Aug],

ISNULL([9],0) as [Sep],

ISNULL([10],0) as [Oct],

ISNULL([11],0) as [Nov],

ISNULL([12],0) as [Dec],

GrandTotal = p.[1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]

FROM

(SELECT T2.state, sum(T1.[Doctotal]) AS [BAL],MONTH(T1.Docdate) as [month]

FROM

[dbo].[OCRD]  T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode]

INNER JOIN CRD1 T2 ON T0.[CardCode] = T2.[CardCode]

Where  T0.[CardType] ='C' AND Year(T1.Docdate) = 2012

Group by

T1.DocDate,

t2.State)S

PIVOT

(SUM(S.[BAL]) FOR [month] IN

         ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Order By

P.State,

p.[1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]

Thanks