GROUP BY WITH TO_CHAR FUNCTION-CodePudding

SELECT
  TO_CHAR(order_date, 'YYYY-MM'), 
  product_id,
  SUM(quantity) sum_quan,
  ROW_NUMBER() OVER ( ORDER BY order_date) RN
FROM customer_orders
WHERE  1 = 1
AND product_id = 2
AND To_char(order_date, 'YYYY-MM') BETWEEN To_char(Add_months(order_date, -1), 'YYYY-MM') AND To_char(order_date, 'YYYY-MM')
GROUP BY  1,2;

HI, I have problem with GROUP BY with TO_CHAR FUNCTION. Does anyone know how this can be fixed?

CodePudding user response：

There are two main problems with your query:

You are grouping by the numbers 1 and 2. You may think that you are grouping by the columns at the positions 1 and 2, but this is not the case. There is no positional GROUP BY in Oracle.
You are invoking a window function on order_date, but this column is not in your intermediate results. It is neither grouped by nor aggregated. You probably want TO_CHAR(order_date, 'YYYY-MM') instead.

Another problem is a semantical one:

WHERE TO_CHAR(order_date, 'YYYY-MM') BETWEEN ... AND TO_CHAR(order_date, 'YYYY-MM')

This is true for every row, because each order date matches itself (except for NULL, but I guess there are no orders without an order date in your table). Maybe you want to compare with the current date? Are you trying to read all orders ordered last month or this month? But then, why BETWEEN? It shouldn't be possible to find any orders made in the future. The newest order in the table can maximally be of today.

The corrected query:

SELECT
  TO_CHAR(order_date, 'YYYY-MM'),
  product_id,
  SUM(quantity) AS sum_quan,
  ROW_NUMBER() OVER (ORDER BY TO_CHAR(order_date, 'YYYY-MM')) AS rn
FROM customer_orders
WHERE 1 = 1
AND product_id = 2
AND order_date >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
GROUP BY TO_CHAR(order_date, 'YYYY-MM'), product_id
ORDER BY TO_CHAR(order_date, 'YYYY-MM'), product_id;

CodePudding user response：

Maybe you just need this, and I am guessing here. And I do not understand why you have the row_number()

SELECT 
od,
product_id,
SUM(quantity) quan
from
(

SELECT 
TO_CHAR(order_date, 'YYYY-MM') as od, 
quantity,
product_id, 
ROW_NUMBER() OVER ( ORDER BY order_date) RN  
FROM customer_orders
WHERE  1 = 1
AND product_id = 2
AND To_char(order_date, 'YYYY-MM') 
BETWEEN To_char(Add_months(order_date, -1), 'YYYY-MM') 
AND To_char(order_date, 'YYYY-MM')
)T
GROUP BY   od,product_id;

CodePudding user response：

You should have to use this method for grouping GROUP BY TO_CHAR(order_date, 'YYYY-MM'), product_id Oracle doesn't provide positional grouping. Oracle provide positional notation to do ordering by using order by clause