CODEWITHSUNDEEP
mysql
rwkiii
rwkiii

Reputation: 5846

MySQL SQL Create Temporary Table with Column and Row Totals

I'm working with a MySQL 5.0 server that supports many VB6 clients. There is a need to create a very simple application to output totals of a few key tables. I'm not an SQL guru by any means, but as simple as this seems to be I'm running into various errors.

The objective is to create a temporary table containing aggregates from various tables in the database. Each row needs a Total column (on the right) and each column needs a total in the bottom row.

Desired output:

         Account      Debit        Credit       Total
1        350          1000.94      0            -1000.94
2        103          0            551.34       551.34
3        356          0            10200.41     10200.41
...
14       000          1000.94      10751.75     9750.81

Current MySQL SQL works for totaling rows, but unable to figure out how to create totals row (#14 above):

SET @PCName := "REPORTS";

Create temporary table TempTable( Account int, Debit int, Credit int, Total int);
(
SELECT * FROM (
    SELECT 1 as seq, '350' as Account, COALESCE(SUM(invoice_amount),0) as Debit, 0 as Credit, COALESCE(SUM(invoice_amount),0) * -1 as Total FROM invoice WHERE Voided = 0
    Union all
    SELECT 2 as seq, '103' as Account, 0 as Debit, COALESCE(SUM(check_amount),0) as Credit, COALESCE(SUM(check_amount),0) as Total FROM auctiondbh.Checks
    Union all
    SELECT 3 as seq, '356' as Account, 0 as Debit, COALESCE(SUM(Amount),0) as Credit, COALESCE(SUM(Amount),0) as Total FROM ScratchBal WHERE rec_Owner = @PCName AND RowType = 'BFC'
    Union all
    SELECT 4 as seq, '554' as Account, 0 as Debit, COALESCE(SUM(Amount),0) as Credit, COALESCE(SUM(Amount),0) as Total FROM ScratchBal WHERE rec_Owner = @PCName AND RowType = 'FEED' AND Description = 'Total Feed'
    Union all
    SELECT 5 as seq, '505' as Account, 0 as Debit, COALESCE(SUM(Amount),0) as Credit, COALESCE(SUM(Amount),0) as Total FROM ScratchBal WHERE rec_Owner = @PCName AND RowType = 'TOT_COMM'
    Union all
    SELECT 6 as seq, '525' as Account, 0 as Debit, COALESCE(SUM(Amount),0) as Credit, COALESCE(SUM(Amount),0) as Total FROM ScratchBal WHERE rec_Owner = @PCName AND RowType = 'CUSTOM_CHARGES' AND  description = 'Commingle%'
    -- ONE ATTEMPT FOLLOWS:
    -- union all
    -- select 14 as seq, '000' as Account, (SELECT SUM(Debit)) AS Debit, (SELECT SUM(Credit)) AS Credit, ((SELECT SUM(Debit)) - (SELECT SUM(Credit))) AS Total
    ) x
);
-- ANOTHER ATTEMPT FOLLOWS:
-- select * FROM TempTable
-- union all
-- select 14 as seq, '000' as Account, (SELECT SUM(debit)) AS Debit, (SELECT SUM(credit)) AS Credit, ((SELECT SUM(debit)) - (SELECT SUM(credit))) AS Total from TempTable

How do I fix this SQL to create row 14 with totals of the Debit, Credit and Total columns?

Upvotes: 0

Views: 484

Answers (1)

IVO GELOV
IVO GELOV

Reputation: 14269

You will have to repeat all the 13 computations in order to compute the totals:

SET @PCName := "REPORTS";

Create temporary table TempTable( Account int, Debit int, Credit int, Total int);
(
SELECT * FROM (
    SELECT 1 as seq, '350' as Account, COALESCE(SUM(invoice_amount),0) as Debit, 0 as Credit, COALESCE(SUM(invoice_amount),0) * -1 as Total FROM invoice WHERE Voided = 0
    Union all
    SELECT 2 as seq, '103' as Account, 0 as Debit, COALESCE(SUM(check_amount),0) as Credit, COALESCE(SUM(check_amount),0) as Total FROM auctiondbh.Checks
    Union all
    SELECT 3 as seq, '356' as Account, 0 as Debit, COALESCE(SUM(Amount),0) as Credit, COALESCE(SUM(Amount),0) as Total FROM ScratchBal WHERE rec_Owner = @PCName AND RowType = 'BFC'
    Union all
    SELECT 4 as seq, '554' as Account, 0 as Debit, COALESCE(SUM(Amount),0) as Credit, COALESCE(SUM(Amount),0) as Total FROM ScratchBal WHERE rec_Owner = @PCName AND RowType = 'FEED' AND Description = 'Total Feed'
    Union all
    SELECT 5 as seq, '505' as Account, 0 as Debit, COALESCE(SUM(Amount),0) as Credit, COALESCE(SUM(Amount),0) as Total FROM ScratchBal WHERE rec_Owner = @PCName AND RowType = 'TOT_COMM'
    Union all
    SELECT 6 as seq, '525' as Account, 0 as Debit, COALESCE(SUM(Amount),0) as Credit, COALESCE(SUM(Amount),0) as Total FROM ScratchBal WHERE rec_Owner = @PCName AND RowType = 'CUSTOM_CHARGES' AND  description = 'Commingle%'
    union all
    select 14 as seq, '000' as Account, SUM(Debit) AS Debit, SUM(Credit) AS Credit, SUM(Debit - Credit) AS Total FROM
    (
    SELECT 1 as seq, '350' as Account, COALESCE(SUM(invoice_amount),0) as Debit, 0 as Credit, COALESCE(SUM(invoice_amount),0) * -1 as Total FROM invoice WHERE Voided = 0
    Union all
    SELECT 2 as seq, '103' as Account, 0 as Debit, COALESCE(SUM(check_amount),0) as Credit, COALESCE(SUM(check_amount),0) as Total FROM auctiondbh.Checks
    Union all
    SELECT 3 as seq, '356' as Account, 0 as Debit, COALESCE(SUM(Amount),0) as Credit, COALESCE(SUM(Amount),0) as Total FROM ScratchBal WHERE rec_Owner = @PCName AND RowType = 'BFC'
    Union all
    SELECT 4 as seq, '554' as Account, 0 as Debit, COALESCE(SUM(Amount),0) as Credit, COALESCE(SUM(Amount),0) as Total FROM ScratchBal WHERE rec_Owner = @PCName AND RowType = 'FEED' AND Description = 'Total Feed'
    Union all
    SELECT 5 as seq, '505' as Account, 0 as Debit, COALESCE(SUM(Amount),0) as Credit, COALESCE(SUM(Amount),0) as Total FROM ScratchBal WHERE rec_Owner = @PCName AND RowType = 'TOT_COMM'
    Union all
    SELECT 6 as seq, '525' as Account, 0 as Debit, COALESCE(SUM(Amount),0) as Credit, COALESCE(SUM(Amount),0) as Total FROM ScratchBal WHERE rec_Owner = @PCName AND RowType = 'CUSTOM_CHARGES' AND  description = 'Commingle%'
    ) w
  ) x
);

Upvotes: 1

Related Questions