使用时间戳按天划分的MySQL组结果

本文介绍了使用时间戳按天划分的MySQL组结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要进行以下查询，并提取总订单数和按天分组的订单总和.我正在使用时间戳存储所有内容.

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

我需要按天分组，但是当我在过去一个周末的销售额中进行结算时，我需要将order_count设为1而不是3.如何才能按天将上述值分组?

注意:爆破仅用于定义时间段(在创建的位置> = TIMESTAMP和< = TIMESTAMP)

更新

没有GROUP BY day

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

使用GROUP BY day

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

我需要此查询来返回每天的销售额，多少订单以及这些销售额的总和.我在这里某处缺少一个难题....

解决方案

您只是忘了在最后添加GROUP BY ...吗?

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

注意:

由于day是MySQL函数，因此不能将as day用于日列.使用类似order_day的东西.

独角兽

根据@OMG Unicorn的评论，您可以使用:

DAY(FROM_UNIXTIME(created)) as `day`

只要将day包装在`反引号中即可.

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

解决方案

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.