GROUP BY WITH TO_CHAR FUNCTION

Question

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?

DhruvJoshi already guessed that you have some problem with this. They are asking *what* the problem is. Unfortunately we cannot read minds. Tell us exactly what happens that you are not satisfied with. — Thorsten Kettner, Apr 22 '22 at 05:37
I only want to group after a year and a month without days. When i have used TO_CHAR IN group by i have error — Grzegorzko, Apr 22 '22 at 05:46
Okay. You get an error. But you don't want to tell us which? Do you want to make this a guessing game? Tell us the exact error message you are getting. — Thorsten Kettner, Apr 22 '22 at 05:49

score 1 · Answer 1 · answered Apr 22 '22 at 05:55

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;

score 0 · Answer 2 · answered Apr 22 '22 at 05:53

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;

score 0 · Answer 3 · answered Apr 22 '22 at 06:13

0

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

answered Apr 22 '22 at 06:13

Kashif Aslam

1
1

GROUP BY WITH TO_CHAR FUNCTION

3 Answers3