CODEWITHSUNDEEP
sqldatabasems-accesspivotcrosstab
BobbyJones
BobbyJones

Reputation: 1364

Inserting/Adding Column totals (sum) at the bottom of the table after a PIVOT

I really need your help to accomplish adding a separate new row to my table after running a PIVOT command (Crosstab Query).

My objective is to count the column totals of each of the [Request Types] and insert the sum of the column into a new row at the bottom of my table.

This seems like rocket science to me and is above and beyond my capabilities and knowledge of the SQL language/realm.

Here's a quick screenshot of the data in my table:

enter image description here

Here is a quick screenshot of the data captured from the table after running a CrossTab Query of TableA

TRANSFORM Count([Division]) AS CountOfDivision
SELECT [Branch], [Division]
FROM TableA
GROUP BY [Branch], [Division]
PIVOT [Request Type]

enter image description here

Here is the desired result/expected outcome of what I am trying to accomplish:

enter image description here

Upvotes: 0

Views: 1133

Answers (1)

EastOfJupiter
EastOfJupiter

Reputation: 781

As stated in a comment to the original post, I am limited to using SQL Server and not MS Access. However, the suggestion was to use a UNION statement in the query for MS Access - which this example still provides a valid way to answer the question.

To create a "Total" line below all of the individual records, an aggregate query needs to be written using the same SQL (with the addition of aggregate functions to the columns to be 'totaled).

I am using CASE statements here to make the logic a little simpler to digest without the added complexity of a cross-tab and pivot.

SELECT   A.Branch
        ,A.Division
        ,CASE WHEN A.RequestType = 'Report' THEN 1 ELSE 0 END AS Report
        ,CASE WHEN A.RequestType = 'Financial Analysis' THEN 1 ELSE 0 END AS FinancialAnalysis
        ,CASE WHEN A.RequestType = 'Letter' THEN 1 ELSE 0 END AS Letter
        ,CASE WHEN A.RequestType = 'Research Paper' THEN 1 ELSE 0 END AS ResearchPaper
  FROM   TableA

UNION

SELECT   'TOTAL'
        ,NULL
        ,SUM(CASE WHEN A.RequestType = 'Report' THEN 1 ELSE 0 END) AS Report
        ,SUM(CASE WHEN A.RequestType = 'Financial Analysis' THEN 1 ELSE 0 END) AS FinancialAnalysis
        ,SUM(CASE WHEN A.RequestType = 'Letter' THEN 1 ELSE 0 END) AS Letter
        ,SUM(CASE WHEN A.RequestType = 'Research Paper' THEN 1 ELSE 0 END) AS ResearchPaper
  FROM   TableA

The results of this query will look like this:

Branch  Division    Report  FinancialAnalysis   Letter  ResearchPaper
ASFA    ASFAA       1       0                   0       0
ASFA    ASFAB       1       0                   0       0
ASFA    ASFAD       0       0                   0       1
ASFB    ASFBE       0       0                   1       0
ASFC    ASFCC       0       1                   0       0
TOTAL   NULL        2       1                   1       1

This works by taking all of the tabulated columns form the first query and querying them a second time, but also adding them up. This produces only a single row - which is appended to the first query.

When using MS Access, the CASE statement I've used above will not work unless you're coding this logic in VBA, and even then the syntax is different. The SQL code in MS access will need to use the SWITCH or IIF functions instead

Upvotes: 1

Related Questions