group by date part of datetime and get number of records for each

[#1]

I have this so far:

select created_at,
DATEDIFF(TO_DATE(current_date()), TO_DATE(sales_flat_order.created_at)) as delay,
count(*) over() as NumberOfOrders 
FROM
magentodb.sales_flat_order 
WHERE
status IN ( 'packed' , 'cod_confirmed' )
GROUP BY TO_DATE(created_at)

But this is not working.

1. syntax error:

Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 1:7 Invalid table alias or column reference 'created_at': (possible column names are: (tok_function to_date (tok_table_or_col created_at)))

2. count(*) does not give sum for each grouped by date but instead all of the rows.

[#2]

can you try with group by TO_DATE(sales_flat_order.created_at) -- considering sales_flat_order as table alias

[#3]

Na. did not work

[#4]

can you post 'show create table table_name' here?

[#5 · Accepted solution]

Hi @Simran Kaur. In your query you are trying to Group By "TO_DATE(created_at)" but the select statement does not retrieve that data. You are retrieving "created_at" and "DATEDIFF(TO_DATE(current_date()), TO_DATE(sales_flat_order.created_at))"

If you add "TO_DATE(created_at)" to your select list or changed your select list to use "TO_DATE(created_at)" instead of "created_at"... it should work.

[#6]

select TO_DATE(created_at),
       DATEDIFF(TO_DATE(current_date()), TO_DATE(sales_flat_order.created_at)) as delay,
       count(*) as NumberOfOrders 
FROM
   magentodb.sales_flat_order 
WHERE
   status IN ( 'packed' , 'cod_confirmed' )
GROUP BY TO_DATE(created_at),
         DATEDIFF(TO_DATE(current_date()), TO_DATE(sales_flat_order.created_at))