how to compare values in givne table

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

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

how to compare values in givne table

Author

Topic

vinod.n.kobal
Starting Member

5 Posts

Posted - 2011-05-10 : 07:54:04

hello,
the table structure is as follows
table name is education

edu_id|stid_id|qual_id|percentage|
----------------------------------
1 | 2 | 1 | 60 |
2 | 2 | 2 | 70 |
3 | 2 | 3 | 72 |
4 | 3 | 1 | 90 |
5 | 3 | 3 | 40 |
6 | 5 | 2 | 60 |

now i want to find those student who scores the more than 60%
now the fable field qual_id 1 is for 10th 2 is for 12th 3 for PG etc the each student has some qualification, now the problem is that i want such a query which display the student Id who scores the more than 60% in each respective class

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-10 : 09:11:00

TRY THIS

CREATE TABLE #T (edu_id INT,STID_ID INT,QUAL_ID INT,PERCENTAGE INT)
INSERT INTO #T
SELECT 1 , 2 , 1 , 70 UNION ALL
SELECT 2 , 2 , 2 , 70 UNION ALL
SELECT 3 , 2 , 3 , 72 UNION ALL
SELECT 4 , 3 , 1 , 90 UNION ALL
SELECT 5 , 3 , 3 , 80 UNION ALL
SELECT 6 , 3 , 2 , 60

SELECT STID_ID FROM (sELECT STID_ID,COUNT(STID_ID) AS A FROM #T WHERE PERCENTAGE>60 GROUP BY STID_ID) T WHERE A=3

DROP TABLE #T

In Love... With Me!

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-10 : 09:16:57

OR THIS

;With cte as
(
Select STID_ID,COUNT(STID_ID) AS ACYEAR FROM #T WHERE PERCENTAGE>60 GROUP BY STID_ID
)
SELECT * FROM CTE WHERE ACYEAR=3

In Love... With Me!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources