非分组表达式:不清楚如何解决问题

Question

我不确定这里的主要问题，相信我的代码是正确的，但我不知道如何解决提供的问题；ORA-00979：不是GROUP BY表达式"；。

SELECT distinct customer_city, customer_state, COUNT(*)
FROM customers
WHERE customer_state not in ('TX', 'OR')
GROUP BY ROLLUP(customer_state);

Answer 1

"独特的"；不适合作为群体表达；您的"；分组依据"；子句需要包括所有未分组的列。试试这个：

SELECT customer_city, customer_state, COUNT(*)
FROM customers
WHERE customer_state not in ('TX', 'OR')
GROUP BY ROLLUP (customer_city, customer_state);

Answer 2

在ROLL UP中，所选列必须是分组的一部分，如下所示。

SELECT customer_city, customer_state, COUNT(*)
FROM customers
WHERE customer_state not in ('TX', 'OR')
GROUP BY ROLLUP(customer_city, customer_state);