MySQL Group Results by day using timestamp

Question

I need to take the following query and pull the total order counts and sum of the orders grouped by day. I'm storing everything using timestamps.

SELECT     COUNT(id) as order_count,     SUM(price + shipping_price) as order_sum,     DAY(FROM_UNIXTIME(created)) as day FROM `order` WHERE '.implode(' AND ', $where).' 

I need to group by DAY but when I do for this past weekend's sales it takes my order_count and makes it 1 instead of 3. How can I pull the above values grouped by day?

NOTE: The implode is used ONLY to define the time period (WHERE created >= TIMESTAMP AND <= TIMESTAMP)

Update

Without GROUP BY day

Array (      [order_count] => 3     [order_sum] => 69.70     [day] => 17 ) 

With GROUP BY day

Array (      [order_count] => 1     [order_sum] => 24.90     [day] => 17 ) 

I need this query to return each day that had sales, how many orders, and the sum of those sales. I'm missing a piece of the puzzle here somewhere....

Answer 1

Are you just forgetting to add GROUP BY ... at the end?

SELECT     COUNT(id) as order_count,     SUM(price + shipping_price) as order_sum,     DAY(FROM_UNIXTIME(created)) as order_day FROM `order` WHERE '.implode(' AND ', $where).' GROUP BY order_day 

NOTE:

You cannot use as day for your day column because day is a MySQL function. Use something like order_day.

Of Unicorns

Per @OMG Unicorn's comment, you can use:

DAY(FROM_UNIXTIME(created)) as `day` 

So long as wrap day in ` backticks.