Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Madhavi.extarc
Starting Member
7 Posts |
Posted - 2014-04-16 : 05:45:53
|
Hi, I'm newbie to sql. Can you help me in resolving this queryScenario : Say i have 3 columns in a table. courseId1,courseId2, number of clashesCREATE TABLE `course_clash` ( `COURSE_REFERENCE1` , `COURSE_REFERENCE2` , `NUM_COURSE_CLASHES` ) INSERT INTO course_clash (COURSE_REFERENCE1, COURSE_REFERENCE2) VALUES ('111', '222');INSERT INTO course_clash (COURSE_REFERENCE1, COURSE_REFERENCE2) VALUES ('222', '111');INSERT INTO course_clash (COURSE_REFERENCE1, COURSE_REFERENCE2) VALUES ('333', '222');INSERT INTO course_clash (COURSE_REFERENCE1, COURSE_REFERENCE2) VALUES ('444', '333');ColumnReference1 holds unique values where columnReference2 can hold duplicate values.Now, it should show the occurrence of a 111 in columnRefernce2 as num of clasheseg: for 111 num of clashes 1 for 222 num of clashes 2 for 333 num of clashes 1 for 444 num of clashes 0Can anybody help me out with the query in resolving the above scenerio. Madhavi |
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2014-04-16 : 06:02:40
|
SELECT COURSE_REFERENCE1 ,(SELECT COUNT(COURSE_REFERENCE2) FROM course_clash WHERE COURSE_REFERENCE2 = CC.COURSE_REFERENCE1) AS NUM_COURSE_CLASHES FROM course_clash AS CCGROUP BY COURSE_REFERENCE1Veera |
|
|
Madhavi.extarc
Starting Member
7 Posts |
Posted - 2014-04-16 : 06:15:49
|
Thank you veera.and is that possible to get at which combination we have a clash. like... did the clash happen at (1,1) or (1,2) or (1,3) or(1,4) similarly for the 2nd 3rd and 4thMadhavi |
|
|
|
|
|
|
|