SQL - Why am I getting an ORA-00918 Error? I defined a column that's duplicated-CodePudding

I'm trying to complete an exercise for an assignment for my Database Programming class using Oracle LiveSQL. I defined the column that appears in each of the 2 tables that are accessed. I do not understand why I'm getting the error. Where am I going wrong with my code?

SELECT 
    orders.customer_id,
    cust_email,
    COUNT(order_id) AS num_orders
FROM
    oe.customers
    LEFT JOIN oe.orders
        ON oe.customers.customer_id = oe.orders.customer_id
GROUP BY
    customer_id
ORDER BY
    customer_id ASC;

The table diagram with the 2 tables we are using:

CodePudding user response：

In general, whatever columns appear in the SELECT clause must also appear in GROUP BY, unless those columns appear inside aggregate functions. Consider using this version:

SELECT 
    c.customer_id,
    c.cust_email,
    COUNT(o.order_id) AS num_orders
FROM oe.customers c
LEFT JOIN oe.orders o
    ON c.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.cust_email
ORDER BY
    c.customer_id;

Note also another problem you had was that customer_id is a column which appears in both tables. You need an alias in order to qualify which table's column you want to include.