Query SQL Server count value of count

Question

I have query as below:

SELECT 
    COUNT(*)
FROM 
    (SELECT 
         h2.hacker_id, COUNT(c2.challenge_id) AS co
     FROM 
         hackers h2 
     INNER JOIN 
         challenges c2 ON h2.hacker_id = c2.hacker_id
     GROUP BY 
         h2.hacker_id
     HAVING 
         COUNT(c2.challenge_id) = 1)

But, my query can not execute well and I get this error:

Msg 102, Level 15, State 1, Server WIN-ILO9GLLB9J0, Line 33
Incorrect syntax near ')'

Please help me resolve this.

Msg 102 comes from SQL Server, so I edited accordingly

Gordon Linoff
– Gordon Linoff

2018-03-15 17:39:23 +00:00
Commented Mar 15, 2018 at 17:39 — Gordon Linoff
– Gordon Linoff, Commented Mar 15, 2018 at 17:39

ScaisEdge · Accepted Answer · 2018-03-16 06:54:57Z

1

You need a table name for the FROM ( ) table eg add a simple T after the closing )

SELECT COUNT(*)
FROM (
    SELECT h2.hacker_id, count(c2.challenge_id) as co
    FROM hackers h2 INNER JOIN challenges c2 ON h2.hacker_id = c2.hacker_id
    GROUP BY h2.hacker_id
    HAVING count(c2.challenge_id) = 1
) T

edited Mar 16, 2018 at 6:54

answered Mar 15, 2018 at 17:35

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2018-03-15 17:35:03Z

You need an alias for the subquery in either SQL Server or MySQL:

SELECT COUNT(*)
FROM (SELECT h2.hacker_id, count(c2.challenge_id) as co
      FROM hackers h2 INNER JOIN 
           challenges c2
           ON h2.hacker_id = c2.hacker_id
      GROUP BY h2.hacker_id
      HAVING count(c2.challenge_id) = 1
     ) x;

Assuming that your hacker_ids are all well defined, you do not need the JOIN:

SELECT COUNT(*)
FROM (SELECT c2.hacker_id, count(c2.challenge_id) as co
      FROM challenges c2
      GROUP BY c2.hacker_id
      HAVING count(c2.challenge_id) = 1
     ) x;

If you have a unique id in challenges, you can also phrase this as:

select count(*)
from challenges c
where not exists (select 1
                  from challenges c2
                  where c2.hacker_id = c.hacker_id and c2.challenge_id <> c.challenge_id
                 );

With an index on challenges(hacker_id, challenge_id), this should have the best performance (once again, regardless of database).

Collectives™ on Stack Overflow

Query SQL Server count value of count

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related