CODEWITHSUNDEEP
sqldatabasems-accessrowstranspose
BobbyJones
BobbyJones

Reputation: 1364

What would be the SQL code to add a new table row for column totals

I really need your help as this question is way, above and beyond my level of knowledge as it concerns the SQL world.

I'd like to amend and format my existing SQL Crosstab query such that I would be able include the addition of a new table row that would allow for the sum of the each of the column totals (that would produce the result in the figure below entitled "CROSSTAB TABLE A w/ TOTALS")

CROSSTAB - TABLE A is product of the following current SQL Query (with data processed from Table A)

TRANSFORM Count(TableA.[Division]) AS CountOfDivision
SELECT TableA.[Branch], TableA.Division
FROM TableA
GROUP BY TableA.[Branch], TableA.Division
PIVOT TableA.[RequestType];

enter image description here

run using MS Access/ADO Jet 4.0 that would take the data from the top table (which is a working example of what is the current data structure in the MDB file) and produce the resulting metrics table in the 2nd table depicted below thus Transposing the [Request Type] Column to Row Headers and Count, then order by Division.

This seems like advanced SQL algebra to me, and goes far beyond my level of SQL knowledge/programming.

Upvotes: 0

Views: 100

Answers (3)

Parfait
Parfait

Reputation: 107707

Consider a union of two aggregate queries -one that groups at Branch and Division level, the other without any grouping for full Total. MS Access's crosstab query can actually be re-written as conditional aggregates (the usual way to pivot in most RDMS's). This of course assumes, the pivoted column is not too many that scripting is not feasible:

SELECT TableA.[Branch], TableA.Division, 
       SUM(IIF(TableA.Division='Report', 1, 0)) As [Report],
       SUM(IIF(TableA.Division='Financial Analysis', 1, 0)) As [Financial Analsyis],
       SUM(IIF(TableA.Division='Research Paper', 1, 0)) As [Research Paper]
FROM TableA
GROUP BY TableA.[Branch], TableA.Division;

UNION

SELECT 'Total' As Branch, '' As Division,
       SUM(IIF(TableA.Division='Report', 1, 0)) As [Report],
       SUM(IIF(TableA.Division='Financial Analysis', 1, 0)) As [Financial Analsyis],
       SUM(IIF(TableA.Division='Research Paper', 1, 0)) As [Research Paper]
FROM TableA;

Upvotes: 0

JJ32
JJ32

Reputation: 1034

I'm building on the answer from @Matt here. I would base the crosstab off a union query:

SELECT Branch, Division, RequestType, 1 AS CountOfDivision
FROM TableA

UNION

SELECT 'Total' AS Branch, NULL AS Division, RequestType, 
    Sum(1) AS CountOfDivision
FROM TableA
GROUP BY RequestType

Then you could do something like this:

TRANSFORM Sum(CountOfDivision) AS Counts
SELECT Branch, Division
FROM MyUnionQuery
GROUP BY Branch, Division
PIVOT RequestType

Finally you'll need to add some logic to sort the totals row to the bottom.

Upvotes: 0

Matt
Matt

Reputation: 14361

In SQL you would do some fancy fun stuff with CUBE/ROLLUPs etc.. But I don't know what is or is not accessible in ms-access. What I would do is build 2-3 views. 1 that provides your Cross table. 1 That gets all of the Totals and 1 that union all query 1 & 2. You can of course combine all of those to a single query but I find in access that can be challenging sometimes.

If this is for a report or Excel then the report or Excel would be the appropriate place to create the totals row.

Upvotes: 1

Related Questions