使用时间戳按天划分的MySQL组结果 [英] MySQL Group Results by day using timestamp
问题描述
我需要进行以下查询,并提取总订单数和按天分组的订单总和.我正在使用时间戳存储所有内容.
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.
这篇关于使用时间戳按天划分的MySQL组结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!