SQL Count total given a Where/Having parameter

Question

Ive got the follow sql which counts how many actors have more than 1 role based on actorID

SELECT COUNT (actorID) 
from ROLE
GROUP BY actorID
HAVING COUNT (actorID) > 3;

It gives me the followin result which is correct

COUNT(ACTORID)
         4
         5
         4
         4

However I WANT it to give me the total of 4 (4 actors have more than 3 film roles)

MatBailie · Accepted Answer · 2012-02-20 10:36:14Z

2

You have to do this in two steps.

First, identify the actors with more than 3 films, as you have done. Then count those records in a separate query...

SELECT
  COUNT(*)
FROM
(
  SELECT   actorID
  FROM     ROLE
  GROUP BY actorID
  HAVING   COUNT(*) > 3
)
  AS actors

answered Feb 20, 2012 at 10:36

MatBailie

87.6k19 gold badges113 silver badges144 bronze badges

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

1 Comment

user1055487 Over a year ago

Thanks! It didnt like the As Actors at the end but other worked! perfect!

LenPopLilly · Accepted Answer · 2012-02-20 10:31:52Z

0

Try substituting HAVING for WHERE

answered Feb 20, 2012 at 10:31

LenPopLilly

1,2273 gold badges13 silver badges21 bronze badges

1 Comment

MatBailie Over a year ago

@LillyPop - If you correct your answer, people will remove their -1's. But as it stands your answer suggests trying something syntactically impossible. Because it's a completely incorrect answer, it gets -1 to signify that. But if you alter your answer to be something at least valid, then you don't get -1.

Collectives™ on Stack Overflow

SQL Count total given a Where/Having parameter

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related