MySQL query - using SUM of COUNT

Question

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.

Paul · Accepted Answer · 2011-07-15 16:27:58Z

51

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

edited Jul 15, 2011 at 16:27

answered Jul 15, 2011 at 15:52

Paul

142k28 gold badges285 silver badges272 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

David Ryder Over a year ago

That query executes successfully but it returns a seemingly random number (4999). The query in that subquery run by itself returns 1320. But when I wrap it in the outer select statement it returns 4999.

David Ryder Over a year ago

Actually this is REALLY close. Instead of SUM(count) I did COUNT(count) and it returned what I was looking for. :D If you edit your answer I'll accept it

Paul Over a year ago

I changed, it, but I think this is going to return the number sources which have 2 or more calls, instead of the sum. Is that what you wanted?

David Ryder Over a year ago

Yep, that's exactly what I wanted. The field I was looking for was "Repeat calls"

aloha_mate Over a year ago

@Paulpro I have a similar question stackoverflow.com/questions/40433497/…

Marc B · Accepted Answer · 2011-07-15 15:54:37Z

21

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...
)

answered Jul 15, 2011 at 15:54

Marc B

362k44 gold badges433 silver badges508 bronze badges

3 Comments

fungusanthrax Over a year ago

Great solution!

yogithesymbian Over a year ago

Query 1 ERROR: Every derived table must have its own alias

yogithesymbian Over a year ago

SELECT SUM(count) FROM (SELECT fhmp.posisi_skrg as 'position_jetty', fhmp.model_id as 'form_hulu_migas_id' FROM realisasi_posisi_kapals as fhmp ORDER BY fhmp.id DESC);

Rahul · Accepted Answer · 2011-07-15 16:04:02Z

5

Try this

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

edited Jul 15, 2011 at 16:04

answered Jul 15, 2011 at 15:56

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

2 Comments

David Ryder Over a year ago

Unknown column 'count' in 'having clause' edit: it looks like you forgot to change count in the having clause to mycount. It ran after that but it's still returning 4999.

Rahul Over a year ago

Edited, it shoule be mycount. Sorry.

sagi · Accepted Answer · 2011-07-15 15:54:52Z

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.

answered Jul 15, 2011 at 15:54

sagi

5,7621 gold badge34 silver badges31 bronze badges

Comments

user3162020 · Accepted Answer · 2014-03-21 14:58:29Z

2

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

answered Mar 21, 2014 at 14:58

user3162020

525 bronze badges

Collectives™ on Stack Overflow

MySQL query - using SUM of COUNT

5 Answers 5

5 Comments

3 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related