BobbyJones BobbyJones - 5 months ago 17
SQL Question

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.

Answer

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.