CODEWITHSUNDEEP
sqlsql-serversql-server-2008sumcrosstab
Justin
Justin

Reputation: 972

SQL SUM Down Rows and Sum of Sum Across Columns

I am trying to create a query in SQL server 2008...better explained with some table example:

FinPeriodNr   BalJan   BalFeb   BalMar   BalApr

   1           100      100      200      400
   2           200      300      100      200

**

Question

**

I want to be able to sum up the columns first to look like this:

FinPeriodNr   BalJan   BalFeb   BalMar   BalApr

Sum of rows    300      400      300      600

Then be able to sum up the columns to give a total balance like this:

FinPeriodNr   TotalSumofBal

                  1600

Is it possible to get this all summed up in SQL under one query and if so what would I need to use? a crosstab?

The code I have so far only gets me to the second table

SELECT     dbo.Company.CompanyID, dbo.Company.CompanyName,     SUM(dbo.SalesSummaryLedgerCOGBP.BalJan) AS SumBalJan, 
                  SUM(dbo.SalesSummaryLedgerCOGBP.BalFeb) AS SumBalFeb,     SUM(dbo.SalesSummaryLedgerCOGBP.BalMar) AS SumBalMar, 
                  SUM(dbo.SalesSummaryLedgerCOGBP.BalApr) AS SumBalApr, SUM(dbo.SalesSummaryLedgerCOGBP.BalMay) AS SumBalMay, 
                  SUM(dbo.SalesSummaryLedgerCOGBP.BalJun) AS SumBalJun, SUM(dbo.SalesSummaryLedgerCOGBP.BalJul) AS SumBalJul, 
                  SUM(dbo.SalesSummaryLedgerCOGBP.BalAug) AS SumBalAug, SUM(dbo.SalesSummaryLedgerCOGBP.BalSep) AS SumBalSep, 
                  SUM(dbo.SalesSummaryLedgerCOGBP.BalOct) AS SumBalOct, SUM(dbo.SalesSummaryLedgerCOGBP.BalNov) AS SumBalNov, 
                  SUM(dbo.SalesSummaryLedgerCOGBP.BalDec) AS SumBalDec

FROM         dbo.FinancialPeriodCOGBP INNER JOIN
                  dbo.SalesSummaryLedgerCOGBP ON dbo.FinancialPeriodCOGBP.FinPeriodNr = dbo.SalesSummaryLedgerCOGBP.FinPeriodNr INNER JOIN
                  dbo.Company ON dbo.SalesSummaryLedgerCOGBP.CompanyID = dbo.Company.CompanyID

GROUP BY dbo.Company.CompanyName, dbo.Company.CompanyID, dbo.Company.Customer, dbo.FinancialPeriodCOGBP.FinStatusOpen

HAVING      (dbo.Company.Customer = 1) AND (dbo.FinancialPeriodCOGBP.FinStatusOpen = 1)

Please let me know if there a solution to this since I have tried a lot of this but to no avail.

Upvotes: 1

Views: 1123

Answers (2)

Developer
Developer

Reputation: 190

select 
sum(FinPeriodNr) as  FinPeriodNr 
, sum(BalJan) + sum(BalFeb) + sum(BalMar) + sum(BalApr) as TotalSumofBal
FROM
Tble

Upvotes: 1

Jaaz Cole
Jaaz Cole

Reputation: 3180

I would suggest two things, WITH ROLLUP, and using a table alias. There's no shortcut for the column to total the year, but the shortcut for the row total is WITH ROLLUP in the GROUP BY clause. Also, based on the query you posted, you aren't selecting nor ordering by the two columns in your HAVING clause, so I removed them from GROUP BY and put them in a WHERE clause. This is what I came up with.

SELECT
    Company.CompanyID, Company.CompanyName,
    SUM(SaleSL.BalJan) AS SumBalJan,    SUM(SaleSL.BalFeb) AS SumBalFeb,
    SUM(SaleSL.BalMar) AS SumBalMar,    SUM(SaleSL.BalApr) AS SumBalApr,
    SUM(SaleSL.BalMay) AS SumBalMay,    SUM(SaleSL.BalJun) AS SumBalJun,
    SUM(SaleSL.BalJul) AS SumBalJul,    SUM(SaleSL.BalAug) AS SumBalAug,
    SUM(SaleSL.BalSep) AS SumBalSep,    SUM(SaleSL.BalOct) AS SumBalOct,
    SUM(SaleSL.BalNov) AS SumBalNov,    SUM(SaleSL.BalDec) AS SumBalDec,
    SUM(
        SaleSL.BalJan + SaleSL.BalFeb + SaleSL.BalMar + SaleSL.BalApr
        SaleSL.BalMay + SaleSL.BalJun + SaleSL.BalJul + SaleSL.BalAug
        SaleSL.BalSep + SaleSL.BalOct + SaleSL.BalNov + SaleSL.BalDec
    ) AS SumBalTotal
FROM
    dbo.FinancialPeriodCOGBP FP
        INNER JOIN dbo.SalesSummaryLedgerCOGBP SaleSL ON FP.FinPeriodNr = SaleSL.FinPeriodNr
        INNER JOIN dbo.Company ON SaleSL.CompanyID = Company.CompanyID
WHERE (Company.Customer = 1) AND (FP.FinStatusOpen = 1)
GROUP BY Company.CompanyName, Company.CompanyID WITH ROLLUP

Upvotes: 0

Related Questions