CODEWITHSUNDEEP
sqlsql-servert-sql
Srinivasa Rao
Srinivasa Rao

Reputation: 172

Group by, deriving only one data set and more than one dataset

Have a table as follows

StudentID    Name  Subject
1            AAA   Computers
2            BBB   Computers
2            BBB   Electronics
3            CCC   Electronics

I want load this data, into two tables as below OnlyOneSubject Table

StudentID    Name  Subject
1            AAA   Computers
3            CCC   Electronics

MoreThanOneSubject Table 

StudentID    Name  Subject
2            BBB   Computers
2            BBB   Electronics

My sql as below, not sure how to extract data from this below query.

 SELECT *,row_number() over(partition by Name order by Subject) FROM 
 STUDENTS

Upvotes: 0

Views: 29

Answers (2)

Nolan Shang
Nolan Shang

Reputation: 2328

you can use:

WITH STUDENTS(StudentID,Name,Subject)AS(
   SELECT 1,'AAA','Computers' UNION ALL 
   SELECT 2,'BBB','Computers' UNION ALL 
   SELECT 2,'BBB','Electronics' UNION ALL 
   SELECT 3,'CCC','Electronics'
)
select * from (
   SELECT *,count(0) over(partition by Name) as cnt FROM STUDENTS
) as t where cnt=1  ---or cnt>1 for MoreThanOneSubject

StudentID   Name    Subject cnt
1   AAA Computers   1
3   CCC Electronics 1

Upvotes: 1

S3S
S3S

Reputation: 25132

For the first one...

select *
from table
where StudentID in (select StudentID from table group by StudentID having count(*) = 1)

And the second one...

select *
from table
where StudentID in (select StudentID from table group by StudentID having count(*) > 1)

But I'd really only use this to return results... no need to divide a table that'd you have to constantly update.

Upvotes: 2

Related Questions