SQL Help needed

Jym · **Joined:** Sat Oct 01, 2005 5:19 pm **Posts:** 3216

I can't figure this out for the life of me. I can do it in RB but would really prefer having the database engine do it so I don't have to walk through 28,000 students taking anywhere from 8 to 45 courses.

Using Postgres
I have 1 view and 1 table
student_grades_view(grades_student_rowid INTEGER, course TEXT, date_taken DATE) //there are more but not important to the SQL
deans_list(deans_student_rowid INTEGER, date_approved DATE)

I need the count of the courses taken after the last time the student was on the Dean's List. If they have never been on the list the result would be all the courses the student has taken since they started.

example data: student_grades
1 | b1 | 2012-01-01
1 | b1 | 2011-01-01
1 | b1 | 2010-01-01
1 | b1 | 2010-01-01

2 | b1 | 2012-01-01

3 | b1 | 2012-01-01
3 | b1 | 2011-01-01
3 | b1 | 2010-01-01
3 | b1 | 2009-01-01
3 | b1 | 2008-01-01

deans_list
1 | 2010-01-01
3 | 2008-01-01
3 | 2010-05-05

result recordset
1 | 2 //student 1 has taken 2 courses since 2010-01-01
2 | 1 //student 2 has never been on the list all his courses count
3 | 2 //student 3 we only care about after 2010-05-05 and has taken 2 course after that

Anyone have a clue how I SQL this?

TIA

timhare · **Posted:** Fri May 17, 2013 7:13 pm

select grades_student_rowid, count(*) from student_grades_view
where date_taken > (select max(date_approved) from deans_list where deans_student_rowid = grades_student_rowid)
group by grades_student_rowid

You'll have to deal with a null returned from the subquery. I don't know which postgres function you would use for that. mySQL has an IFNULL() function you would use. I don't know the postgres equivalent.

DaveS · **Posted:** Fri May 17, 2013 7:55 pm

SELECT grades_student_rowid,COUNT(*) AS course_cnt
  FROM STUDENT_GRADES,
  DEANS_LIST
  WHERE grades_student_rowid=deans_student_rowid
  AND date_taken>date_approved
  GROUP BY grades_student_rowid
  UNION
  SELECT Grades_student_rowid,COUNT(*) AS course_cnt
    FROM STUDENT_GRADES
    WHERE NOT EXISTS(SELECT 8
    FROM DEANS_LIST
    WHERE grades_student_rowid=deans_student_rowid)
    GROUP BY grades_student_row_id;

Tim's code didn't cover if they had NEVER been on deans list before.

Jym · **Joined:** Sat Oct 01, 2005 5:19 pm **Posts:** 3216

Hi Tim,
Thanks I tried that one last night sometime, but tried again and it's only giving me 1 row of data. So I'm guessing the MAX is the issue, I've tried joins, and you name it, I can't figure out what's wrong.

Jym · **Joined:** Sat Oct 01, 2005 5:19 pm **Posts:** 3216

Hi Dave, I'm going to try that in about 20 mins. does it take into consideration a student can have up to 4 dates in the deans list and I only care about the latest date?

DaveS · **Posted:** Fri May 17, 2013 8:19 pm

No I missed that requirement.... but basically take what Tim had
and the portion of mine from UNION on.... append them into one query and that should work

The part before the UNION counts the students on the Deans list... after the Union is students that were never on the list

DaveS · **Posted:** Fri May 17, 2013 8:22 pm

try this

SELECT grades_student_rowid,COUNT(*) AS course_cnt
  FROM STUDENT_GRADES,
  DEANS_LIST a
  WHERE grades_student_rowid=deans_student_rowid
  AND date_taken>date_approved
  AND date_approved=(SELECT MAX(date_approved)
  FROM DEANS_LIST b
  WHERE a.deans_student_rowid=b.deans_student_rowid)
  GROUP BY grades_student_rowid
  UNION
  SELECT Grades_student_rowid,COUNT(*) AS course_cnt
    FROM STUDENT_GRADES
    WHERE NOT EXISTS(SELECT 8
    FROM DEANS_LIST
    WHERE grades_student_rowid=deans_student_rowid)
    GROUP BY grades_student_row_id;

notice that DEANS_LIST is used TWICE in the top part (so make sure you add the A and B alias)

This is off the top of my head... so you might need to tweek it some

and yes that is "SELECT 8" not "SELECT *" in that one line..... no need to fill a data vector if you just care if it exists or not

timhare · **Posted:** Fri May 17, 2013 8:37 pm

Looks like postgres supports coalesce, so

select grades_student_rowid, count(*) from student_grades_view
where date_taken > coalesce((select max(date_approved) from deans_list where deans_student_rowid = grades_student_rowid),'1900-01-01')
group by grades_student_rowid

Jym · **Joined:** Sat Oct 01, 2005 5:19 pm **Posts:** 3216

Thank you both, both work perfectly and produce the exact same recordset. I can now get in the car and go up to the cottage where there's no internet for the rest of the long weekend and work on the GUI

Jym · **Joined:** Sat Oct 01, 2005 5:19 pm **Posts:** 3216

FYI it runs 3 times faster if I use WITH instead of the actual tables.

	Real Software Forums The forum for Real Studio and other Real Software products.
[ REAL Software Website \| Board Index ]

Real Software Forums

SQL Help needed