我需要进行以下查询并提取总订单数和按天分组的订单总和.我使用时间戳存储所有内容.
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).'我需要按 DAY 分组,但是当我为上周末的销售进行分组时,它需要我的 order_count 并将其设为 1 而不是 3.如何提取按天分组的上述值?
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?
注意:内爆仅用于定义时间段(创建位置 >= TIMESTAMP AND <= TIMESTAMP)
NOTE: The implode is used ONLY to define the time period (WHERE created >= TIMESTAMP AND <= TIMESTAMP)
更新
没有 GROUP BY day
Without GROUP BY day
Array ( [order_count] => 3 [order_sum] => 69.70 [day] => 17 )使用 GROUP BY day
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....
推荐答案你是不是忘了在最后加上GROUP BY ...?
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注意:
您不能将 as day 用于您的 day 列,因为 day 是 MySQL 函数.使用 order_day 之类的内容.
NOTE:
You cannot use as day for your day column because day is a MySQL function. Use something like order_day.
根据@OMG Unicorn 的评论,您可以使用:
Per @OMG Unicorn's comment, you can use:
DAY(FROM_UNIXTIME(created)) as `day`只要将 day 包裹在 ` 反引号中.
So long as wrap day in ` backticks.
更多推荐
MySQL 使用时间戳按天分组结果
发布评论