Mysqli 选择平均计数查询 [英] Mysqli select average of count query

问题描述

我正在尝试获得销售的独特商品的平均数量.数据库结构如下:

I am attempting to get the average number of unique items sold. The database structure is like:

ID    ItemSize    ItemColor    DateSold

如果我做这个查询:

SELECT Count(*) AS COUNT FROM store_sales GROUP BY ItemSize, ItemColor

... 这将为我提供每个尺寸和颜色相同的商品的总销量.

... This will give me the total sold for each item whose size and color are the same.

我需要获取此结果集中所有数字的平均值.我该怎么做?

I need to get the AVERAGE of all the numbers in this result set. How would I do this?

** 样本数据 **

store_sales:

ID    ItemSize    ItemColor
1     3           5
2     3           5
3     2           5
4     2           6
5     2           6
6     2           6

查询:

Select Count(*) AS COUNT FROM store_sales GROUP BY ItemSize, ItemColor

结果:

Array ( 
  [0] => Array ( [Count] => 2 ) 
  [1] => Array ( [Count] => 1 ) 
  [2] => Array ( [Count] => 3 ) 
) 

我正在尝试获得平均值.我想返回 2.

I am attempting to get the average. I would like to return 2.

推荐答案

只需将您的查询用作 AVG 的子查询，并且不要将列命名为 COUNT保留字.

Just use your query as subquery for the AVG, and dont name a column COUNT that is a reserved word.

SQL Fiddle 演示

SELECT AVG(ITEM_COUNT)
FROM (
        SELECT Count(*) AS ITEM_COUNT
        FROM store_sales 
        GROUP BY ItemSize, ItemColor
     ) T

这篇关于Mysqli 选择平均计数查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！