question group by date part of datetime and get number of records for each in Support Questions

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

simran_k — Thu, 16 Jun 2016 16:18:41 GMT

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.

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

rajkumar_singh — Thu, 16 Jun 2016 16:23:30 GMT

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

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

simran_k — Thu, 16 Jun 2016 16:25:52 GMT

Na. did not work

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

rajkumar_singh — Thu, 16 Jun 2016 16:32:07 GMT

can you post 'show create table table_name' here?

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

bpreachuk — Thu, 16 Jun 2016 22:06:21 GMT

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.

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

bpreachuk — Thu, 16 Jun 2016 23:17:54 GMT

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))