I want to make sum's after each periode ( i want to sum up "Total DKK", "Teus" and "pr. teu")
I have the following SQL:
SELECT "STAT$DAN_2013".PERIOD, "STAT$DAN_2013".VESSEL_NAME AS "Skibsnavn", "STAT$DAN_2013".VOYAGE_NO AS "Voyage", SUM("STAT$DAN_2013".CREDIT_AMOUNT) - SUM("STAT$DAN_2013".DEBET_AMOUNT) AS "Total DKK", SUM("STAT$DAN_2013".TEUS) AS "Teus", (SUM("STAT$DAN_2013".CREDIT_AMOUNT) - SUM("STAT$DAN_2013".DEBET_AMOUNT)) / SUM("STAT$DAN_2013".TEUS) AS "Pr. teu"
FROM SCAN."STAT$DAN_2013" "STAT$DAN_2013"
WHERE "STAT$DAN_2013".DEPARTMENT=107 AND "STAT$DAN_2013".PERIOD<1307
GROUP BY rollup("STAT$DAN_2013".PERIOD, "STAT$DAN_2013".VESSEL_NAME, "STAT$DAN_2013".VOYAGE_NO)
ORDER BY "STAT$DAN_2013".PERIOD, "STAT$DAN_2013".VESSEL_NAME, "STAT$DAN_2013".VOYAGE_NO;
But i get an output like this:
PERIOD Skibsnavn Voyage Total DKK Teus Pr. teu 1301 HANJIN ASIA 0004W 10.00 61 493.29 1301 HANJIN ASIA 10.00 61 493.29 1301 HANJIN ITALY 0010W 60.00 53 510.49 1301 HANJIN ITALY 60.00 53 510.49 1301 HANJIN SPAIN 0009W 100.00 74 526.11 1301 HANJIN SPAIN 100.00 74 526.11 1301 170.00 188 511.06 1302 HANJIN AFRICA 0003W 5.00 87 642.62 1302 HANJIN AFRICA 5.00 87 642.62 1302 HANJIN EUROPE 0004W 40.00 131 473.40 1302 HANJIN EUROPE 40.00 131 473.40 1302 HANJIN KOREA 0014W 70.00 80 617.18 1302 HANJIN KOREA 70.00 80 617.18 1302 HANJIN NETHERLANDS 0010W 60.00 57 778.15 1302 HANJIN NETHERLANDS 60.00 57 778.15 1302 HANJIN UNITED KINGDOM 0008W 80.00 83 520.64 1302 HANJIN UNITED KINGDOM 80.00 83 520.64 1302 255.00 438 581.88
i would like this output:
PERIOD Skibsnavn Voyage Total DKK Teus Pr. teu 1301 HANJIN ASIA 0004W 10.00 61 493.29 1301 HANJIN ITALY 0010W 60.00 53 510.49 1301 HANJIN SPAIN 0009W 100.00 74 526.11 1301 170.00 188 511.06 1302 HANJIN AFRICA 0003W 5.00 87 642.62 1302 HANJIN EUROPE 0004W 40.00 131 473.40 1302 HANJIN KOREA 0014W 70.00 80 617.18 1302 HANJIN NETHERLANDS 0010W 60.00 57 778.15 1302 HANJIN UNITED KINGDOM 0008W 80.00 83 520.64 1302 255.00 438 581.88
Is that possible?
Try this:
SELECT "STAT$DAN_2013".PERIOD, "STAT$DAN_2013".VESSEL_NAME AS "Skibsnavn", "STAT$DAN_2013".VOYAGE_NO AS "Voyage", SUM("STAT$DAN_2013".CREDIT_AMOUNT) - SUM("STAT$DAN_2013".DEBET_AMOUNT) AS "Total DKK", SUM("STAT$DAN_2013".TEUS) AS "Teus", (SUM("STAT$DAN_2013".CREDIT_AMOUNT) - SUM("STAT$DAN_2013".DEBET_AMOUNT)) / SUM("STAT$DAN_2013".TEUS) AS "Pr. teu"
FROM SCAN."STAT$DAN_2013" "STAT$DAN_2013"
WHERE "STAT$DAN_2013".DEPARTMENT=107 AND "STAT$DAN_2013".PERIOD<1307
GROUP BY rollup("STAT$DAN_2013".PERIOD, ("STAT$DAN_2013".VESSEL_NAME, "STAT$DAN_2013".VOYAGE_NO))
ORDER BY "STAT$DAN_2013".PERIOD, "STAT$DAN_2013".VESSEL_NAME, "STAT$DAN_2013".VOYAGE_NO;
I've added parentheses around the last two columns in the ROLLUP.
I highly recommend this article by Tim Hall: Cube, Rollup and GROUPING functions