I have the following table in sql-server database:
I want to count the total number of rows where the column (state = Alabama and ID is not repeated).
For this example from Alabama is 2.
And I want to count the number of ID's that are repeated (here it is 2).
SELECT count(id), state FROM table_name WHERE id IN (SELECT id FROM table_name GROUP BY id having count(id) = 1) and state = 'Alabama' GROUP BY state 
AND State='Alabama'
use
SELECT COUNT(DISTINCT ID) AS id FROM MYTABLE where State = 'Alabama' if you need count of id's that are repeated
SELECT COUNT(ID) AS id FROM MYTABLE where ID= '105' 
select count(*), State from Table where ID in (select ID from Table group bu ID having count(*)=1) and State is 'Alabama' group by State 
DECLARE @test TABLE ( id int , name varchar(20) ); INSERT INTO @test VALUES (101,'Albama'); INSERT INTO @test VALUES (102,'Montana'); INSERT INTO @test VALUES (103,'Montana'); INSERT INTO @test VALUES (104,'Albama'); INSERT INTO @test VALUES (105,'Albama'); INSERT INTO @test VALUES (105,'Albama'); WITH CTE AS ( select id,name,ROW_NUMBER()OVER (PARTITION BY ID ORDER BY NAME DESC) AS RN from @test ) select COUNT(id),name from CTE WHERE NAME = CASE WHEN ID = 105 THEN name ELSE NAME END GROUP BY NAME 
Create Table myTable ( [ID] [int], [State] [nvarchar](255) ); INSERT INTO myTable(ID,[State]) VALUES(101,'Alabama'); INSERT INTO myTable(ID,[State]) VALUES(102,'Montana'); INSERT INTO myTable(ID,[State]) VALUES(103,'Monatana'); INSERT INTO myTable(ID,[State]) VALUES(103,'Alabama'); INSERT INTO myTable(ID,[State]) VALUES(104,'Alabama'); INSERT INTO myTable(ID,[State]) VALUES(105,'Monatana'); INSERT INTO myTable(ID,[State]) VALUES(105,'Alabama'); Use the below Query to get the required result.
SELECT COUNT(DISTINCT ID) AS ID, [State] FROM myTable WHERE ID IN (SELECT ID FROM myTable GROUP BY ID having COUNT(ID) = 1) and [State] = 'Alabama' GROUP BY [State] 
where ID is not repeated? Do you only need IDs 101 and 105?