Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL query - using SUM of COUNT

Tags:

mysql

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

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.

like image 687
David Ryder Avatar asked Jul 15 '11 15:07

David Ryder


People also ask

How do I count total data in MySQL?

To counts all of the rows in a table, whether they contain NULL values or not, use COUNT(*). That form of the COUNT() function basically returns the number of rows in a result set returned by a SELECT statement.

What the difference between sum and count in SQL?

Sum is doing the mathematical sum, whereas count simply counts any value as 1 regardless of what data type.


2 Answers

SELECT COUNT(count) FROM (SELECT COUNT(source) AS count
FROM call_details
GROUP BY source
HAVING count > 1) as A
like image 194
Paul Avatar answered Oct 05 '22 10:10

Paul


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.

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:

SELECT SUM(count) FROM (
   SELECT original query here...
)
like image 20
Marc B Avatar answered Oct 05 '22 10:10

Marc B