alter session set nls_date_format = 'dd/mm/yyyy';

ACCEPT input_year PROMPT 'Enter the year to check the quantity of each menu item ordered by customers (YYYY): ' 

col menu_id                format a10
col description            format a50
col total_sales_amount     format 9999.99

TTITLE CENTER 'Sales of Menu Item ordered by customers in ' &input_year ' compared with previous year' RIGHT 'Page:' SQL.PNO SKIP 2

WITH YearlyOrderData AS (
  SELECT
    m.MENU_ID,
    m.DESCRIPTION AS MENU_DESCRIPTION,
    TO_CHAR(o.ORDER_DATE, 'YYYY') AS ORDER_YEAR,
    SUM(oi.QUANTITY * oi.UNIT_PRICE) AS TOTAL_SALES_AMOUNT
  FROM
    orders o
    JOIN order_item oi ON o.ORDER_NO = oi.ORDER_NO
    JOIN menus m ON oi.MENU_ID = m.MENU_ID
  GROUP BY
    m.MENU_ID, m.DESCRIPTION, TO_CHAR(o.ORDER_DATE, 'YYYY')
),
DistinctMenuItems AS (
  SELECT DISTINCT MENU_ID, DESCRIPTION
  FROM menus
),
SELECT
  dmi.MENU_ID,
  dmi.DESCRIPTION AS MENU_DESCRIPTION,
  TO_CHAR(MAX(CASE WHEN yod.ORDER_YEAR = '&input_year' THEN yod.TOTAL_SALES_AMOUNT ELSE 0 END), '$999,999.99') AS "&input_year",
  TO_CHAR(MAX(CASE WHEN yod.ORDER_YEAR = TO_CHAR(TO_NUMBER('&input_year') - 1) THEN yod.TOTAL_SALES_AMOUNT ELSE 0 END), '$999,999.99') AS "Prev Year"
FROM
  DistinctMenuItems dmi
  LEFT JOIN YearlyOrderData yod ON dmi.MENU_ID = yod.MENU_ID
    AND (yod.ORDER_YEAR = '&input_year' OR yod.ORDER_YEAR = TO_CHAR(TO_NUMBER('&input_year') - 1))
GROUP BY
  dmi.MENU_ID, dmi.DESCRIPTION
ORDER BY
  dmi.MENU_ID;

上面是我的代码。输出将显示year“&input_year”和“prev year”中每个菜单项的total_sales_amount，如下所示：

Sales of Menu Item ordered by customers in 2023 compared with previous year       Page:         1

MENU_ID    MENU_DESCRIPTION                                                                 2023         Prev Year
---------- -------------------------------------------------------------------------------- ------------ ------------
BBSL       Blanched Bean Sprout Large                                                              $4.50       $12.00
BBSS       Blanched Bean Sprout Small                                                              $4.00        $7.00
BR1        Sourdough Potato Bread                                                                 $10.00        $9.00
BR2        Sourdough Loaf                                                                         $59.50         $.00
BR3        Sourdough Cheese Bread                                                                  $5.50        $9.00
C1C        CHAM O COLD                                                                            $16.00       $47.00
C1H        CHAM O HOT                                                                             $35.00       $21.00
C2C        CHAM COLD                                                                                $.00       $26.00
C2H        CHAM HOT                                                                                 $.00         $.00
C3C        CHAM C COLD                                                                            $36.50       $40.00
C3H        CHAM C HOT                                                                             $16.00         $.00
CF2        Chee Cheong Fun Set                                                                      $.00         $.00
CF3        Chee Cheong Fun Set + CharSiu                                                          $37.50      $152.00
DHC        Drumstick Hainan Chicken Rice                                                            $.00         $.00
G01        Chicken and Pork Horfun                                                                $11.90       $59.50
G02        Old Style Ipoh Gai Si Horfun                                                          $111.80      $114.80
GP1        Special Horfun                                                                        $150.00       $72.00
HC0        Hainan Chicken Rice                                                                      $.00       $13.00
HHC        one half Hainan Chicken                                                                  $.00         $.00
K1C        KOPI O COLD                                                                              $.00       $20.00
K1H        KOPI O HOT                                                                             $28.00       $31.00
K2C        KOPI COLD                                                                               $6.50       $36.00
K2H        KOPI HOT                                                                               $16.00        $4.30
K3C        KOPI C COLD                                                                              $.00         $.00
K3H        KOPI C HOT                                                                             $20.00        $3.00
K4C        KOPI SARAWAK COLD                                                                      $27.50       $22.50
K4H        KOPI SARAWAK HOT                                                                         $.00       $46.00
KB1        Black Kampua (With Kampua Daoyu)                                                       $25.00         $.00
KB2        Black Kampua with Gyoza                                                                  $.00       $90.00
KB3        Black Kampua withHainan Chicken                                                        $70.40       $83.50

它们是两列。我想显示这两列的总和。有人能帮我吗？我是否应该使用Cockte语法？
我试着使用过的，但不知何故，它不工作。也许我写错了声明。