New topic: SQL Help needed
<http://forums.realsoftware.com/viewtopic.php?t=47910> Page 1 of 1 [ 9 posts ] Previous topic | Next topic Author Message Jym Post subject: SQL Help neededPosted: Fri May 17, 2013 6:40 pm Joined: Sat Oct 01, 2005 5:19 pm Posts: 3204 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 Top timhare Post subject: Re: SQL Help neededPosted: Fri May 17, 2013 7:13 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 12340 Location: Portland, OR USA 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. Top DaveS Post subject: Re: SQL Help neededPosted: Fri May 17, 2013 7:55 pm Joined: Sun Aug 05, 2007 10:46 am Posts: 4890 Location: San Diego, CA 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. _________________ Dave Sisemore iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1 Note : I am not interested in any solutions that involve custom Plug-ins of any kind Top Jym Post subject: Re: SQL Help neededPosted: Fri May 17, 2013 7:58 pm Joined: Sat Oct 01, 2005 5:19 pm Posts: 3204 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. Top Jym Post subject: Re: SQL Help neededPosted: Fri May 17, 2013 8:01 pm Joined: Sat Oct 01, 2005 5:19 pm Posts: 3204 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? Top DaveS Post subject: Re: SQL Help neededPosted: Fri May 17, 2013 8:19 pm Joined: Sun Aug 05, 2007 10:46 am Posts: 4890 Location: San Diego, CA 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 _________________ Dave Sisemore iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1 Note : I am not interested in any solutions that involve custom Plug-ins of any kind Top DaveS Post subject: Re: SQL Help neededPosted: Fri May 17, 2013 8:22 pm Joined: Sun Aug 05, 2007 10:46 am Posts: 4890 Location: San Diego, CA 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 _________________ Dave Sisemore iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1 Note : I am not interested in any solutions that involve custom Plug-ins of any kind Top timhare Post subject: Re: SQL Help neededPosted: Fri May 17, 2013 8:37 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 12340 Location: Portland, OR USA 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 Top Jym Post subject: Re: SQL Help neededPosted: Fri May 17, 2013 9:10 pm Joined: Sat Oct 01, 2005 5:19 pm Posts: 3204 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 Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 9 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
