Intermediate SQL

Once you're familiar with basic SQL, the following should help you do more.  Everything listed below will work in sqlReports as well:

Decode

You may be familiar with a decode in PS for things such as object reports, and there's a similar version for Oracle SQL.  You include the field, the value from it, and the result you want to output.   Below is an example of outputting Female and Male instead of the F and M values from the gender field:

SELECT lastfirst, decode(gender,'F','Female','M','Male')
FROM students

If there's a value in the field and no result, the record will still print but there will be a null value for that column.  For example:

SELECT lastfirst, decode(gender,'F','Female')
FROM students

Will outpul all the students and Female will be in the second column if there's a F in gender, but nothing will appear in the column for others.  You can include a result at the end and anything that is not defined will receive that value.  Below is an example of outputting Freshman for 9th graders and Upperclassmen for 10th - 12th graders:

SELECT lastfirst, decode(grade_level,9,'Freshman','Upperclassman')
FROM students
WHERE grade_level > 8

Since the result for 9 is defined, it'll use it's defined result, and for all other grade levels it'll put Upperclassman.  The above example also shows if the value is numeric, just put the value, whereas with the other examples, when the value is alphanumeric (for example, M or F for gender), then you put the values in single quotes.


Case

Case can be used like a decode in that it allows you to define the way you want a value to be displayed.  For example, the gender example above in decode could be written like so with case:

SELECT lastfirst,
CASE when gender='M' then 'Male'
         when gender='F' then 'Female'
         end genderdetails
FROM students

You start with the word case and have the word end at the end - you can use an alias after the word end if you're using it in a client such as Oracle SQL Developer to shorten the column heading.  Every line in between case and end starts with when, followed by the criteria, and then the output when it's true.  You can also include an else.  Below is an example of the grade level decode from above using case instead:

SELECT lastfirst,
CASE when grade_level = 9 then 'Freshman' else 'Upperclassman' end
FROM students
WHERE grade_level > 8

The main advantage case has over decode is it can do more than just what the value equals and it can do multiple things after the when:

SELECT lastfirst, grade_level,
CASE when grade_level > 8 and schoolid = 100 then 'AGHS'
         when grade_level > 8 and schoolid = 200 then 'AGHS2'
         when grade_level between 6 and 8 and schoolid = 600 then 'CHMS'
         else 'All Others' end
FROM students


Distinct

To not show duplicates for a field, you put the word distinct before the field in the select statement.  For example, to find the unique name of cities for all the active students, use the following:

SELECT DISTINCT city
FROM students
WHERE enroll_status=0
ORDER BY city

Count

To get a count of the number of records that match your criteria, use count(*) in the select statement. For example:

SELECT count(*)
FROM students
WHERE enroll_status=0


Group By

Group by is used to group things together.  For example, to get total students by school we could use the following:

SELECT schoolid, count(*)
FROM students
WHERE enroll_status=0
GROUP BY schoolid
ORDER BY schoolid

If we add more fields to the select, we have to add them to the group by as well.  For example, if we want to break the data down by grade_level, we could add grade_level into the mix:

SELECT schoolid, grade_level, count(*)
FROM students
WHERE enroll_status=0
GROUP BY schoolid, grade_level
ORDER BY schoolid

You don't need to add the additional fields to the order by unless you want to sort the data in a certain order.  Changing the group by above to group by grade_level, schoolid will give you a different output.  Aggregate functions in the select, like count, sum, or max, do not have to be added to group by clauses.


Group By Rollup

The group by rollup is different than the regular group by in that it'll give you the total as a line at the end.  For example, the following will show the schoolid, grade level, and total in that grade for that school on each line, but before moving onto the next school it can add a line showing the overall total for the school:

SELECT schoolid, grade_level, count(*)
FROM students
WHERE enroll_status = 0
GROUP BY ROLLUP (schoolid, grade_level)
ORDER BY schoolid

The group by rollup syntax can give you different results based on how it's written.  For example, you can only put one field in the rollup and get different results.  A good example of using group by rollup and getting different results by changing around the command is fee transactions.  The following will show you dates with fee transactions happening on the date along with totals:

SELECT date_value, feeid, sum(amount)
FROM fee_transaction
GROUP BY ROLLUP date_value, rollup(feeid)

Since date_value is not in the rollup, it only gives totals for each date.  If you also want an overall total, change it to group by rollup(date_value, feeid).

If you'd rather have things grouped together by fees, you can change it to:

SELECT feeid, date_value, sum(amount)
FROM fee_transaction
GROUP BY ROLLUP feeid, rollup(date_value)

The output will show all the different transactions for a fee along with a total and then show the next fee.


Max

Using max comes in handy when you want to list several things on one line without having to use a lengthy and complicated group by clause.  For example, if you wanted to list the S1 and S2 stored grades for a student, you could use the following (if you're using this on actual data, change the 1 after studentid= to an actual student number):

SELECT course_name, grade
FROM storedgrades
WHERE studentid = 1 and storecode in ('S1','S2')
GROUP BY course_name

But that would return one line for each grade - the S1 grade would be on one line and the S2 grade on another.  To get them on the same line, you could use this instead which is saying if there's a grade for that storecode, put the grade and otherwise leave it blank:

SELECT course_name,
MAX(case when storecode='S1' then grade else null end) s1grade,
MAX(case when storecode='S2' then grade else null end) s2grade
FROM storedgrades
WHERE studentid = 3 and storecode in ('S1','S2')
GROUP BY course_name
ORDER BY course_name

The group by is used to combine things together, in this case the course_name.  Usually when you use a group by it'll mirror the select clause, but since max's are used you don't have to include them in the group by.  You may even see some select clauses with max(fieldname) - fieldname is replaced with an actual field name - with the max being used to avoid having to put the fieldname in the group by clause.


Sum

Sum is a way to add up the total amount of what's in a field.  For example, the following would list all of the students with a fee balance and the total balance for each student:

SELECT studentid, sum(balance)
FROM fee_balance
GROUP BY studentid
ORDER BY studentid

To get the student name rather than just the studentid, the students table would need to be added:

SELECT s.lastfirst, sum(fb.balance)
FROM fee_balance fb
INNER JOIN students s on s.id = fb.studentid
GROUP BY fb.studentid, s.lastfirst
ORDER BY s.lastfirst

The above statement shows too that the group by can include fields not in the select clause.  It's better to group by studentid instead of lastfirst because you may have some students with the same name, but unless you want the studentid to be part of the output you can leave it out of the select.

In addition, using sum with case is a way to find the total amount for a specific piece of criteria.  In the example below, the 1's and 0's are added together and a final total is displayed when the query finishes and is an example of one way to create a pivot table:

SELECT course_name,
SUM(case when grade = 'A+' then 1 else 0 end) APlus,
SUM(case when grade = 'A' then 1 else 0 end) AGrade,
SUM(case when grade = 'A-' then 1 else 0 end) AMinus
FROM storedgrades
WHERE storecode = 'S1' and termid >= 2500 and grade in ('A+','A','A-')
GROUP BY course_name
ORDER BY course_name

Like count and max, sum does not have to be included in the group by clause.


Having

Having is commonly used with group by and count or sum to only show information that meets certain criteria.  For example, if you wanted to only show grade levels where there were more than 200 students in the grade, you would have to first count the number of students by grade level and then use having to set the criteria.  In other words, you can't set it in a where clause since you're wanting to look for criteria on an aggregate amount, so having lets you do the criteria.

SELECT grade_level, count(*)
FROM students
WHERE enroll_status=0
GROUP BY grade_level
HAVING count(*) > 200
ORDER BY grade_level

Having could be used to find students with a fee balance greater than a certain amount:

SELECT studentid, sum(balance)
FROM fee_balance
GROUP BY studentid
HAVING sum(balance) > 100
ORDER BY studentid

Or students with more than one F in storedgrades for Q1:

SELECT studentid, count(grade)
FROM storedgrades
WHERE storecode = 'Q1' and grade like 'F%' and termid >= 2500
GROUP BY studentid
HAVING count(grade) > 1
ORDER BY studentid

 

  Back to the List of Articles