November 26, 2012 at 1:07 pm
I have a dataset for students and they have either selected 'Math' or 'Science' or both.
So I have 2 rows, 1 for Math and 1 for Science if they have selected both or 1 row if they have selected either of those 2 subjects.
Now I need to select data for those students who have selected only Math or both and I need to create a marker field (1,0) if they selected both then 1 else 0.
So 1 row for each student and if opted both then 1 in the marker field.
How can I do that?
Thanks,
Blyzzard
November 26, 2012 at 1:18 pm
amar_kaur16 (11/26/2012)
I have a dataset for students and they have either selected 'Math' or 'Science' or both.So I have 2 rows, 1 for Math and 1 for Science if they have selected both or 1 row if they have selected either of those 2 subjects.
Now I need to select data for those students who have selected only Math or both and I need to create a marker field (1,0) if they selected both then 1 else 0.
So 1 row for each student and if opted both then 1 in the marker field.
How can I do that?
Thanks,
Blyzzard
Kind of an odd requirement but this works for your description.
IF OBJECT_ID('TempDB..#MathScience','U') IS NOT NULL
drop table #MathScience
create table #MathScience
(
StudentID int,
SubjectChoice varchar(10)
)
insert #MathScience
select 1, 'Math' union all
select 1, 'Science' union all
select 2, 'Science' union all
select 3, 'Math'
select StudentID, Case when COUNT(*) > 1 then 1 else 0 end as Marker
from #MathScience
group by StudentID
having COUNT(*) > 1 or MIN(SubjectChoice) = 'Math'
Notice how I posted ddl and sample data. That is something you should do in the future to make things easier for us.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 26, 2012 at 2:03 pm
Could you please explain?
Thanks,
Blyzzard
November 26, 2012 at 2:25 pm
amar_kaur16 (11/26/2012)
Could you please explain?Thanks,
Blyzzard
What part don't you understand? I am not even really sure if this meets your criteria.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 26, 2012 at 2:37 pm
Actually I need to select all the fields and rows for Math subject and craete a marker so that if any student has selected Science as well then I it should be 1.
So basically I need to get Math data but need to indicate that some of them has selected both.
November 26, 2012 at 2:46 pm
That is what the query I posted will show you.
Here is another way to get the same results, but it has a bit more complicated execution plan.
select StudentID, Case when COUNT(*) > 1 then 1 else 0 end as Marker
from #MathScience
where StudentID in (select StudentID from #MathScience where SubjectChoice = 'Math')
group by StudentID
If there is something missing then you will need to post YOUR ddl, sample data and desired output. The stuff I am working with is because you didn't provide so I had to make some up.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 26, 2012 at 3:17 pm
Your query is selecting all the students who have selected Math only but the Marker field is empty for all the returned students.
Thus I think that students who have selected both are not being included
Below is the input format
Student Id NameSubject Selection Date
10021 john Math 11/1/2011
10021 John Science 11/12/2011
10022 Becky Math 11/2/2011
10022 Becky Science 11/10/2011
10023 Will Math 11/4/2011
10024 Matt Math 11/5/2011
Output desired:
Student Id NameSubjectSelection DateMarker ( Math& Science)
10021 johnMath11/1/20111
10022 BeckyMath11/2/10111
10023 WillMath11/4/20110
10024 MattMath11/5/20110
I appreciate your help.
Thanks,
Blyzzard
November 27, 2012 at 7:29 am
amar_kaur16 (11/26/2012)
Your query is selecting all the students who have selected Math only but the Marker field is empty for all the returned students.Thus I think that students who have selected both are not being included
Below is the input format
Student Id NameSubject Selection Date
10021 john Math 11/1/2011
10021 John Science 11/12/2011
10022 Becky Math 11/2/2011
10022 Becky Science 11/10/2011
10023 Will Math 11/4/2011
10024 Matt Math 11/5/2011
Output desired:
Student Id NameSubjectSelection DateMarker ( Math& Science)
10021 johnMath11/1/20111
10022 BeckyMath11/2/10111
10023 WillMath11/4/20110
10024 MattMath11/5/20110
I appreciate your help.
Thanks,
Blyzzard
It seems you did not run the query I posted. And you really need to look at the link in my signature about how to post data. The format you posted is not helpful. Here is your data in a consumable format. I added Name to both of the queries I posted and they BOTH return exactly what you are looking for. I removed the selection date from the results because it doesn't make sense, which one will be in the result set when there are 2?
IF OBJECT_ID('TempDB..#NewMathScience','U') IS NOT NULL
drop table #NewMathScience
create table #NewMathScience
(
StudentID int,
Name varchar(20),
SubjectChoice varchar(20),
SelectionDate datetime
)
insert #NewMathScience
select 10021, 'john', 'Math', '2001-11-01' union all
select 10021, 'John', 'Science', '2011-11-12' union all
select 10022, 'Becky', 'Math', '2011-11-02' union all
select 10022, 'Becky', 'Science', '2011-11-10' union all
select 10023, 'Will', 'Math', '2011-11-04' union all
select 10024, 'Matt', 'Math', '2011-11-05'
select StudentID, Name, Case when COUNT(*) > 1 then 1 else 0 end as Marker
from #NewMathScience
group by StudentID, Name
having COUNT(*) > 1 or MIN(SubjectChoice) = 'Math'
select StudentID, Name, Case when COUNT(*) > 1 then 1 else 0 end as Marker
from #NewMathScience
where StudentID in (select StudentID from #NewMathScience where SubjectChoice = 'Math')
group by StudentID, Name
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 27, 2012 at 8:37 am
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply