MySQL查询 - 使用COUNT的SUM

时间:2022-10-21 23:41:41

This query:

这个查询:

SELECT COUNT(source) AS count
FROM call_details
GROUP BY source
HAVING count >1

Returns about 1500 (the number I'm looking for) results with only the count field. How could I also return the sum of all count fields? When I try

返回大约1500(我正在寻找的数字)结果只有count字段。我怎么能还返回所有计数字段的总和?当我尝试

SELECT COUNT(source) AS count,
SUM(count) as total
FROM call_details
GROUP BY source
HAVING count >1

I get an 'Unknown column 'count' in 'field list' error.

我在'字段列表'错误中收到'未知列'计数'。

And

SELECT COUNT(source) AS count,
SUM(COUNT(source)) as total
FROM call_details
GROUP BY source
HAVING count >1

gives me an 'Invalid use of group function'

给我一个'无效使用群组功能'

Any ideas? I can do a mysql_num_rows($result) of the first set (to get the info I need) but I really want to do it through MySQL.

有任何想法吗?我可以做第一组的mysql_num_rows($ result)(以获取我需要的信息),但我真的想通过MySQL来做。

5 个解决方案

#1


34  

SELECT COUNT(count) FROM (SELECT COUNT(source) AS count
FROM call_details
GROUP BY source
HAVING count > 1) as A

#2


14  

You can't get a global total in a row-context. At the time the the COUNT() completes on any particular row, there's nothing to SUM, because the other rows haven't been calculated yet.

您无法在行上下文中获得全局总计。在COUNT()在任何特定行上完成时,没有什么要SUM,因为还没有计算其他行。

You'd have to run the SUM query first to get your individual stats, then sum manually in your script, or re-run the query with a surrounding SUM clause:

您必须先运行SUM查询以获取各自的统计信息,然后在脚本中手动求和,或者使用周围的SUM子句重新运行查询:

SELECT SUM(count) FROM (
   SELECT original query here...
)

#3


2  

Assuming you are going to fetch all the results in the application anyway, I think the most efficient way would be to just sum it up in the application code.

假设您要在应用程序中获取所有结果,我认为最有效的方法是在应用程序代码中总结它。

#4


1  

Try this

尝试这个

select mycount, sum(mycount) as sumcount
from
(SELECT COUNT(source) AS mycount FROM call_details GROUP BY source HAVING mycount >1)   counttable 

#5


1  

Just simply remove the 'Group by' clause in the select query that counts

只需删除重要的选择查询中的“分组依据”子句即可

# first, get your counts by source
SELECT COUNT(source) AS count
FROM call_details
GROUP BY source
HAVING count >1

# then, get the overall total
SELECT COUNT(source) AS count
FROM call_details
HAVING count >1

#1


34  

SELECT COUNT(count) FROM (SELECT COUNT(source) AS count
FROM call_details
GROUP BY source
HAVING count > 1) as A

#2


14  

You can't get a global total in a row-context. At the time the the COUNT() completes on any particular row, there's nothing to SUM, because the other rows haven't been calculated yet.

您无法在行上下文中获得全局总计。在COUNT()在任何特定行上完成时,没有什么要SUM,因为还没有计算其他行。

You'd have to run the SUM query first to get your individual stats, then sum manually in your script, or re-run the query with a surrounding SUM clause:

您必须先运行SUM查询以获取各自的统计信息,然后在脚本中手动求和,或者使用周围的SUM子句重新运行查询:

SELECT SUM(count) FROM (
   SELECT original query here...
)

#3


2  

Assuming you are going to fetch all the results in the application anyway, I think the most efficient way would be to just sum it up in the application code.

假设您要在应用程序中获取所有结果,我认为最有效的方法是在应用程序代码中总结它。

#4


1  

Try this

尝试这个

select mycount, sum(mycount) as sumcount
from
(SELECT COUNT(source) AS mycount FROM call_details GROUP BY source HAVING mycount >1)   counttable 

#5


1  

Just simply remove the 'Group by' clause in the select query that counts

只需删除重要的选择查询中的“分组依据”子句即可

# first, get your counts by source
SELECT COUNT(source) AS count
FROM call_details
GROUP BY source
HAVING count >1

# then, get the overall total
SELECT COUNT(source) AS count
FROM call_details
HAVING count >1