Srinivasa Rao Srinivasa Rao - 3 years ago 84
SQL Question

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

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download