How can I insert values from SELECT SUM into other table?

Question

I'm stuck and I could use a little input. How can I insert the SUM(amount) of all values of "accountKonto" of table "Buchung" into one row of the table "Erfolg"?

"Buchung": id accountKonto amount

"Erfolg": id totalAmountAccountKonto1 totalAmountAccountKonto2 …

For each possible "accountKonto" in "Buchung", there is one column in "Erfolg", into which I need to insert the sum. At the end, I need to have one new row in "Erfolg" that should have all sums of "amount" for each "accountKonto" that exists in "Buchung". Makes sense?

It should begin like this:

SELECT SUM(amount) FROM Buchung …

But how do I tell it to put each sum into the corresponding field of table Erfolg?

Thanks a lot for your help!

Gary

Answer 1

You should combine INSERT .. SELECT with PIVOT.

Using PIVOT (available in SQL Server and Oracle, only):

SELECT *
FROM (
  SELECT accountKonto, amount
  FROM Buchung
) t
PIVOT (
  SUM(amount) FOR accountKonto IN ([1], [2], [3])
) AS p

The above query produces something like:

1      2      3
---------------------
28.00  17.00  15.35

If you're not using SQL Server:

... then you cannot use PIVOT, but you can emulate it easily:

SELECT
  SUM(CASE accountKonto WHEN 1 THEN amount END) totalAmountAccountKonto1,
  SUM(CASE accountKonto WHEN 2 THEN amount END) totalAmountAccountKonto2,
  SUM(CASE accountKonto WHEN 3 THEN amount END) totalAmountAccountKonto3
FROM Buchung

Inserting that into your other table:

Just use INSERT .. SELECT as follows:

INSERT INTO Erfolg (
  totalAmountAccountKonto1,
  totalAmountAccountKonto2,
  totalAmountAccountKonto3
)
SELECT p.[1], p.[2], p.[3]
FROM (
  SELECT accountKonto, amount
  FROM Buchung
) t
PIVOT (
  SUM(amount) FOR accountKonto IN ([1], [2], [3])
) AS p;

... or if PIVOT is not available:

INSERT INTO Erfolg (
  totalAmountAccountKonto1,
  totalAmountAccountKonto2,
  totalAmountAccountKonto3
)
SELECT
  SUM(CASE accountKonto WHEN 1 THEN amount END) AS totalAmountAccountKonto1,
  SUM(CASE accountKonto WHEN 2 THEN amount END) AS totalAmountAccountKonto2,
  SUM(CASE accountKonto WHEN 3 THEN amount END) AS totalAmountAccountKonto3
FROM Buchung