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

Question

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.

Note : I am actually using hive but it is exactly like sql when it comes to queries

Answer 1

Try this:

select 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 Date(created_at)

Answer 2

I think you want to use date part(including year, month and day) of created_at for grouping.

select
date(created_at) as created_at_day,
datediff(curdate(), sales_flat_order.created_at) as delay,
count(*) as numberOfOrders
from magentodb.sales_flat_order
WHERE status IN ('packed', 'cod_confirmed' ) GROUP BY created_at_day

This query will show only the first order created on the day. Because you are grouping by the day. You can use average to find average delay of orders created for the day.

Answer 3

My phone won't allow me to post comments. But try this link it might guide you the right way.

stackoverflow.com/questions/29704904/invalid-table-alias-or-column-reference-b