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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Find the number of clashes

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 query

Scenario :

Say i have 3 columns in a table. courseId1,courseId2, number of clashes

CREATE 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 clashes

eg: for 111 num of clashes 1
for 222 num of clashes 2
for 333 num of clashes 1
for 444 num of clashes 0

Can 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 CC
GROUP BY COURSE_REFERENCE1

Veera
Go to Top of Page

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 4th

Madhavi
Go to Top of Page
   

- Advertisement -