0

I have the following table in sql-server database:

enter image description here

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

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
Eric Klaus
  • 923
  • 1
  • 9
  • 24
  • 2
    possible duplicate of [SQL to find the number of distinct values in a column](http://stackoverflow.com/questions/141562/sql-to-find-the-number-of-distinct-values-in-a-column) – Wim Ombelets Mar 13 '14 at 12:31
  • Not the same problem. I don't want just to count number of distinct ID's. – Eric Klaus Mar 13 '14 at 12:33
  • What do you mean with `where ID is not repeated`? Do you only need IDs 101 and 105? – NickyvV Mar 13 '14 at 12:33
  • Now I get it, you want the count of the IDs that have only the State Alabama and not anything else? – NickyvV Mar 13 '14 at 12:38

5 Answers5

1

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'
Sajad Karuthedath
  • 14,987
  • 4
  • 32
  • 49
1
  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
MikkaRin
  • 3,026
  • 19
  • 34
1
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
Anish Shah
  • 7,669
  • 8
  • 29
  • 40
0
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
mohan111
  • 8,633
  • 4
  • 28
  • 55
0
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]

Live SQL SERVER Fiddle Here.

Suhaib Janjua
  • 3,538
  • 16
  • 59
  • 73
  • What about the number of id's that are duplicated? Could you please help? – Eric Klaus Mar 13 '14 at 13:16
  • By using this query you always gets the unique and distinct records. This query will ignore the repeating Ids. The resultant of this query is based on, 101 and 104 Ids. The record with repeated Ids will be skip in the resultant – Suhaib Janjua Mar 13 '14 at 13:19
  • 3
    This is your statement **I don't want to count Alabama where it is in duplicate ID**. The provided solution is based on your requirements that you have given. – Suhaib Janjua Mar 13 '14 at 13:22