How to combine common dates into 1 row in Oracle SQL

Question

My query is:

SELECT EXTRACT(DAY FROM CO.ORDER_DATE) "DATE",
       TO_CHAR(CO.ORDER_DATE, 'DY') "BUSIEST DAY",
       SUM(OLI.QUANTITY) "NO. OF DISHES"
FROM CUSTOMER_ORDER CO INNER JOIN ORDER_LINE_ITEM OLI
ON CO.ORDER_ID = OLI.ORDER_ID
WHERE CO.ORDER_DATE >= TO_DATE('01 SEPTEMBER 2019', 'DD MONTH YYYY')
AND CO.ORDER_DATE <= TO_DATE('30 SEPTEMBER 2019', 'DD MONTH YYYY')
GROUP BY CO.ORDER_DATE
ORDER BY CO.ORDER_DATE

My current output is:

DATE   BUSIEST DAY    NO. OF DISHES
-----------------------------------
1      SUN            27
1      SUN            10
14     SAT            10

The output I want is:

DATE   BUSIEST DAY    NO. OF DISHES
-----------------------------------
1      SUN            37
14     SAT            10

What changes do I need to make in my Oracle SQL query?

Answer 1

The problem here is that while you are selecting the day of the month, you are actually aggregating by the date. This means that two different dates on the first of the month would end up appearing as two separate records. You should instead aggregate by the actual day value and busiest day values:

SELECT
    EXTRACT(DAY FROM CO.ORDER_DATE) "DATE",
    TO_CHAR(CO.ORDER_DATE, 'DY') "BUSIEST DAY",
    SUM(OLI.QUANTITY) "NO. OF DISHES"
FROM CUSTOMER_ORDER CO
INNER JOIN ORDER_LINE_ITEM OLI
    ON CO.ORDER_ID = OLI.ORDER_ID
WHERE
    CO.ORDER_DATE >= TO_DATE('01 SEPTEMBER 2019', 'DD MONTH YYYY') AND
    CO.ORDER_DATE <= TO_DATE('30 SEPTEMBER 2019', 'DD MONTH YYYY')
GROUP BY
    EXTRACT(DAY FROM CO.ORDER_DATE),
    TO_CHAR(CO.ORDER_DATE, 'DY')
ORDER BY
    "DATE";