January 26, 2017 at 5:52 am
Hi I have two tables
create table Students (StudentID int not null , Name nvarchar(20))
insert into Students values (101111 , 'XYZ'),
(101112 , 'Ben') ,
(101113 , 'Kate'),
(101114 , 'Andy')
Create Table Subscriptions (StudentID int , Subscriptions int)
insert into subscriptions values ( 101111, 3),
(101111,10),
(101112, 8),
(101113 , 4),
(101112,12),
(101114 , 3),
(101114,5)
-- query
select s.StudentID , sum(SS.subscriptions) subscriptions from students s join Subscriptions SS
on s.StudentID= ss.StudentID
group by s.StudentID
having sum(SS.subscriptions) > 10
-- Result
studentId Subscriptions
10111113 13
10111220 20
Now I need to get only the studentId whose subscriptions are > 10 into a new table. The table has only one column StudentID
January 26, 2017 at 5:57 am
SQL-Learner - Thursday, January 26, 2017 5:52 AMHi I have two tablescreate table Students (StudentID int not null , Name nvarchar(20))
insert into Students values (101111 , 'XYZ'),
(101112 , 'Ben') ,
(101113 , 'Kate'),
(101114 , 'Andy')Create Table Subscriptions (StudentID int , Subscriptions int)
insert into subscriptions values ( 101111, 3),
(101111,10),
(101112, 8),
(101113 , 4),
(101112,12),
(101114 , 3),
(101114,5)-- query
select s.StudentID , sum(SS.subscriptions) subscriptions from students s join Subscriptions SS
on s.StudentID= ss.StudentID
group by s.StudentID
having sum(SS.subscriptions) > 10-- Result
studentId Subscriptions
10111113 13
10111220 20Now I need to get only the studentId whose subscriptions are > 10 into a new table. The table has only one column StudentID
First step: write a query to identify studentIds where subscription > 10. Can you do that bit?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 26, 2017 at 5:58 am
INSERT INTO MyTable (StudentID)
select s.StudentID
from Students s
join Subscriptions ss on s.StudentID= ss.StudentID
group by s.StudentID
having sum(ss.Subscriptions) > 10John
January 26, 2017 at 5:58 am
Phil Parkin - Thursday, January 26, 2017 5:57 AMSQL-Learner - Thursday, January 26, 2017 5:52 AMHi I have two tablescreate table Students (StudentID int not null , Name nvarchar(20))
insert into Students values (101111 , 'XYZ'),
(101112 , 'Ben') ,
(101113 , 'Kate'),
(101114 , 'Andy')Create Table Subscriptions (StudentID int , Subscriptions int)
insert into subscriptions values ( 101111, 3),
(101111,10),
(101112, 8),
(101113 , 4),
(101112,12),
(101114 , 3),
(101114,5)-- query
select s.StudentID , sum(SS.subscriptions) subscriptions from students s join Subscriptions SS
on s.StudentID= ss.StudentID
group by s.StudentID
having sum(SS.subscriptions) > 10-- Result
studentId Subscriptions
10111113 13
10111220 20Now I need to get only the studentId whose subscriptions are > 10 into a new table. The table has only one column StudentID
First step: write a query to identify studentIds where subscription > 10. Can you do that bit?
Perhaps I should have read more closely, sorry. Have you created the new table yet?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 26, 2017 at 6:01 am
Out of interest, why would you want to store these in a table? If your data changes, this would mean that your table of student ID's would be stale, and incorrect. Would a view meet your needs better?
What should happen if a student has more than 10 subscription, and later doesn't? What happens if you run this script again, should you put everyone's ID in there again 9meaning duplication, and without an ID, you have no way of disguising them?
This is the simple answer, but consider what i've said above.CREATE TABLE YourNewTable (StudentID);
GO
INSERT INTO YourNewTable
SELECT s.StudentID
FROM students s
JOIN Subscriptions SS ON s.StudentID= ss.StudentID
GROUP BY s.StudentID
HAVING SUM(SS.subscriptions) > 10;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 26, 2017 at 6:05 am
Phil Parkin - Thursday, January 26, 2017 5:58 AMPhil Parkin - Thursday, January 26, 2017 5:57 AMSQL-Learner - Thursday, January 26, 2017 5:52 AMHi I have two tablescreate table Students (StudentID int not null , Name nvarchar(20))
insert into Students values (101111 , 'XYZ'),
(101112 , 'Ben') ,
(101113 , 'Kate'),
(101114 , 'Andy')Create Table Subscriptions (StudentID int , Subscriptions int)
insert into subscriptions values ( 101111, 3),
(101111,10),
(101112, 8),
(101113 , 4),
(101112,12),
(101114 , 3),
(101114,5)-- query
select s.StudentID , sum(SS.subscriptions) subscriptions from students s join Subscriptions SS
on s.StudentID= ss.StudentID
group by s.StudentID
having sum(SS.subscriptions) > 10-- Result
studentId Subscriptions
10111113 13
10111220 20Now I need to get only the studentId whose subscriptions are > 10 into a new table. The table has only one column StudentID
First step: write a query to identify studentIds where subscription > 10. Can you do that bit?
Perhaps I should have read more closely, sorry. Have you created the new table yet?
Yes, I have created and I got the solution. Thanks for helping me all the time
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply