SQL statement

Question

SQL statement

billk

SSC Veteran

Points: 285
More actions
August 19, 2003 at 5:22 am

#81019

i have the following Table
Table A
Student-NumberOfTests-Percent
1-5-5
1-5-10
1-3-20
1-3-30
1-3-40
1-1-50
2-5-5
2-5-10
2-4-20
2-2-30
2-1-40
3-5-5
3-4-10
3-3-20
3-3-30
3-1-40
3-1-50
i need a SELECT statement that returns students with at least
2 number of tests >= 30%
AND
1 number of tests >= 40%
(The nested Select dont work because the number of test and the percent can be any value and any combination from 1 up to 5 different combinations. )

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi billk,

quote:
i have the following Table
Table A
Student-NumberOfTests-Percent
1-5-5
1-5-10
1-3-20
1-3-30
1-3-40
1-1-50
2-5-5
2-5-10
2-4-20
2-2-30
2-1-40
3-5-5
3-4-10
3-3-20
3-3-30
3-1-40
3-1-50
i need a SELECT statement that returns students with at least
2 number of tests >= 30%
AND
1 number of tests >= 40%
(The nested Select dont work because the number of test and the percent can be any value and any combination from 1 up to 5 different combinations. )

I'm assuming you have a typo in the above. Are you looking for

quote:
2 number of tests >= 30%
OR
1 number of tests >= 40%

?

From scratch, will this work?

SELECT your_fields from TableA WHERE ((numberOfTests=2) AND (Percent>=30)) OR ((numberofTest=1) AND(Percent>=40))

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

NPeeters SSChampion Points: 12554 More actions · Answer 2

I guess you are looking for a AND in the query.

Try this



SELECT Student

FROM TableA M

WHERE EXISTS (SELECT 1 FROM TableA S1

              WHERE S1.Student = M.Student

                AND S1.NumberOfTests >= 2

                AND S1.Percent >= 30)

  AND M.NumberOfTests >= 1

  AND M.Percent >= 40

brokenrulz SSCrazy Points: 2261 More actions · Answer 3

Try this.

select student from

a

where number of tests >=30

group by student,percent

having count(marks)>=2

union

select student from

a

where number of tests >=40

group by student,percent

having count(marks)>=1

Pay Respect to People on your way up. For you will meet the same People on your way down.

He who knows others is learned but the wise one is one who knows himself.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hey, I think we should give billk the chance to say what he tries to achieve

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

NPeeters SSChampion Points: 12554 More actions · Answer 5

Just read your post more carefully...

quote:
The nested Select dont work because the number of test and the percent can be any value and any combination from 1 up to 5 different combinations.

Do you mean that you can have between 1 and 5 such conditions?

By the way, is 1 test of 40% also qualify as 1 test of 30%?

NPeeters SSChampion Points: 12554 More actions · Answer 6

I came up with this solution...



--Temp table to hold the criteria

CREATE TABLE #NrPct

(NrOfTests int,

 Pct   int)

--Insert the criteria in the table

INSERT INTO #NrPct (NrOfTests,Pct)

            VALUES (2,30)

INSERT INTO #NrPct (NrOfTests,Pct)

            VALUES (1,40)

--Get the result

SELECT T.Student

FROM Students T

  INNER JOIN

     #NrPct P

  ON  T.NumberOfTests >= P.NrOfTests

  AND T.Pct = P.Pct

GROUP BY T.Student

HAVING COUNT(*) = (SELECT count(*)

                   FROM #NrPct)

billk SSC Veteran Points: 285 More actions · Answer 7

UNION dont work because the number of combinations is not just 2 (perc=30 and numberoftests =2 AND perc=40 and numberoftests =1) but any combination for example

(perc=30 and numberoftests =2

AND perc=40 and numberoftests =3

AND perc=10 and numberoftests =1)

i need something like the following:

SELECT

student

FROM

TableA

WHERE

(perc=30 and numberoftests =2)

AND

(perc=40 and numberoftests =1)

(The above SELECT is just used for example the code dosent work)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

quote:
i need something like the following:
SELECT
student
FROM
TableA
WHERE
(perc=30 and numberoftests =2)
AND
(perc=40 and numberoftests =1)

that's what I meant before with the typo. And with the OR operator.

Doesn't this work?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

billk SSC Veteran Points: 285 More actions · Answer 9

NPeeters,

you are right the only way is to make a temp table (with the criteria)

and then JOIN the 2 tables. your solution is working.

Thanks

bill

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

oh my,....I'm really dumb today!

Can you explain very slowly what the rationale behind this query is?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]