MySQL COUNT() GROUP BY 子查询(?) 用于统计每月多个商品的购买情况

发布于 2024-12-06 04:14:58 字数 532 浏览 0 评论 0原文

我正在使用 MySQL，我想按购买每个项目的月份计算多个项目的购买数量。我的表名为“items_purchased”，项目按“item_id”列出，购买日期是名为“purchase_date”的时间戳。

到目前为止，这是我的查询：

SELECT items_purchased.item_id, items_purchased.purchase_date, 
DATE_FORMAT(DATE(purchase_date), '%m %y') AS monthYear FROM items_purchased

简单英语的结果如下所示：

item_id=3: 2011 年 6 月购买 2 次，2011 年 7 月购买 3 次
item_id=2: 2011 年 6 月购买 1 次，2011 年 7 月购买 7 次

有任何帮助可以帮助我弄清楚如何执行适当的 COUNT() 和/或 GROUP BY 语句（或我可能需要的其他任何内容）来确定每月购买量对于每个 item_id 将不胜感激！

谢谢你，蒂姆

原文

I'm using MySQL and i'd like to count the number of purchases of multiple items by the month in which each item was purchased. My table is called "items_purchased", the items are listed by "item_id", and the purchase date is a timestamp called "purchase_date".

here's my query so far:

SELECT items_purchased.item_id, items_purchased.purchase_date, 
DATE_FORMAT(DATE(purchase_date), '%m %y') AS monthYear FROM items_purchased

The results in plain english would be something like this:

item_id=3: 2 purchases in june 2011, 3 purchases in july 2011
item_id=2: 1 purchase in june 2011, 7 purchases in july 2011

any help to help me figure out how to do the appropriate COUNT() and/or GROUP BY statements (or whatever else i may need) to determine the per month purchases for each item_id would be greatly appreciated!

thank you,
tim

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦里泪两行 2024-12-13 04:14:58

SELECT YEAR(purchase_date) as year, DATE_FORMAT(purchase_date, '%M') as month, COUNT(item_id) as cnt
FROM items_purchased
GROUP BY YEAR(purchase_date), MONTH(purchase_date)
ORDER BY year, month, cnt

如果您想要每个 itemID 计数，请将“item_id”添加到 GROUP BY 子句的末尾。这只会计算在任何给定年份/月份中出现的 itemID 数量，但不会按项目进行细分。

SELECT YEAR(purchase_date) as year, DATE_FORMAT(purchase_date, '%M') as month, COUNT(item_id) as cnt
FROM items_purchased
GROUP BY YEAR(purchase_date), MONTH(purchase_date)
ORDER BY year, month, cnt

If you want per-itemID counts, add 'item_id' to the end of the GROUP BY clause. This'll just count how many itemIDs show up in any given year/month, but not break it down per-item.

回复收藏 0 原文

~没有更多了~