user2575502 user2575502 - 2 months ago 7
SQL Question

how to get returned rows from a sub query

I want to get rows count from follow query:

select c1,count(*) from table
group by c1 having count(*) > 1;


so I write sql statement in SQL server like below:

select count(*) from (
select c1, count(*) from table
group by c1 having count(*) > 1);


but when I run this script, I always get an syntax error that means there is a syntax error near last parenthesis. but this sql statement works fine in Oracle.

can anybody tell me how should I write it in SQL server correctly?

Answer

SQL Server requires alias name to the sub-selects. Add the missing alias name to the sub-select to fix the error.

SELECT Count(*) 
FROM   (SELECT c1, 
               Count(*) cnt
        FROM   table 
        GROUP  BY c1 
        HAVING Count(*) > 1) a; --here  

However there is a simpler version

SELECT TOP 1 Count(*)OVER() -- Distinct Count(*)OVER() 
FROM   table 
GROUP  BY c1 
HAVING Count(*) > 1 
Comments