How to display certain info with this query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to display certain info with this query?

Author

Topic

dboor129
Starting Member

3 Posts

Posted - 2015-03-30 : 23:16:48

SELECT DISTINCT grade, SNAME
FROM Grade_report JOIN Student
ON Grade_report.student_number=Student.stno
WHERE grade LIKE '[a-b]

I need to show the students listed who received more than one A or B. How do I do this? Please show the full syntax to help me. Thanks!

Maithil
Starting Member

29 Posts

Posted - 2015-03-31 : 07:52:59

WIll you Please give your Table Definition and Data so.. We can Work on it....??

Thanks

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-31 : 12:01:29

Maybe this:

select b.sname
      ,c.grade
      ,count(*) as grade_count
  from (select student_number
          from grade_report
         where grade in ('A','B')
         group by student_number
         having count(*)>1
       ) as a
       inner join student as b
               on b.stno=a.student_number
       inner join grade_report as c
               on c.student_number=a.student_number
              and c.grade in ('A','B')
 group by b.sname
         ,c.grade
 order by b.sname
         ,c.grade

If you want to see only the A and B grades, remove the line marked in red.

dboor129
Starting Member

3 Posts

Posted - 2015-03-31 : 12:21:45

Here are my columns from each table:

Grade_report:
-STUDENT_NUMBER
-STUDENT_ID
-GRADE

Student:
-STNO
-SNAME
-MAJOR
-CLASS
-BDATE

Everything is listed exactly as it is in the program

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-31 : 13:24:09

Since this is homework, you need to have a go at it. Post your query if it doesn't work and we'll help you get it working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources