|
Subject:
|
SQL Quries problem
|
|
Posted By:
|
devendar
|
Post Date:
|
1/8/2007 8:39:20 AM
|
Dear Friends,
Query for that wiil read the school12 table and tally the number of schools/district. In addition, If any school in a district has FAIL score, then the status of the distict is FAIL. populate the district table with the results.
populate the dist12 table with the results.
Thanks in advance..
|
|
Reply By:
|
agossage
|
Reply Date:
|
1/8/2007 9:46:10 AM
|
I changed the sid data in the school12 table so that we can join cleanly to the school12 table.
INSERT INTO school12 VALUES(1, 123, 'ABC','FAIL')
INSERT INTO school12 VALUES(2, 123, 'ABC1','PASS')
INSERT INTO school12 VALUES(3, 125, 'XYZ', 'PASS')
INSERT INTO school12 VALUES(4, 125, 'PQR','PASS')
INSERT INTO school12 VALUES(5, 126, 'STU', 'PASS')
One possible solution...
INSERT INTO dist12
SELECT a.did, SchoolCount, ISNULL(c.score, 'PASS')
FROM (SELECT did FROM school12 GROUP BY did) a
JOIN (SELECT did, COUNT(sid) SchoolCount FROM school12 GROUP BY did) b ON a.did = b.did
LEFT JOIN (SELECT did, score FROM school12 WHERE score = 'FAIL') c ON a.did = c.did
Make sure you list me on your homework as a reference... LOL
Adam Gossage
Lake Wylie, SC, USA
|
|
Reply By:
|
devendar
|
Reply Date:
|
1/8/2007 11:37:30 AM
|
Dear agossage
I really appreciate your quick response.Thanks a lot. I 'll get back to you If I need more Information.
|
