Advanced SQL

The following are some advanced things you can do in SQL.  Everything listed below will work in sqlReports as well:

Pivot

Another way to create a pivot table is the Pivot command.  Pivot tables allow you to show information on one row more efficiently than using the case methods mentioned above.  The following pivot table will produce the same thing as the sum(case) example on the Intermediate SQL page - one row per course with a count of A grades:

SELECT course_name, APlus, AGrade, AMinus
FROM
(
SELECT course_name, grade
FROM storedgrades
WHERE storecode = 'S1' and termid >= 2500 and grade in ('A+','A','A-')
)
PIVOT
(count(grade) for grade in ('A+' as APlus,'A' as AGrade, 'A-' as AMinus))
ORDER BY course_name

Using Pivot requires two parts - the first part is the sql statement within the first set of ( ) that gets the information you need. It's best to just return the data you need in it.  For example, one could have left the where part off in the sql statement above, but that would return records that won't be used in the pivot, such as grades other than A grades.  So instead of having the pivot command go through records it won't use, narrow down the output to just the ones it will use.  Create this sql statement first and run it to make sure you're getting data and the results you want.

The second part is the pivot part and it has it's own select and from at the top of the full statement, the Pivot command, and then how you want to create the columns using things like max, sum, count, etc., on the results of the sql statement mentioned in the paragraph above.   When using this in sqlReports, it's important to alias each outcome in the pivot area - such as 'A+' as APlus above - and reference the alias in the pivot's select.  On other sites, you may see SELECT * as the first line, but that won't work in sqlReports and is why you must reference aliases instead. The example above is an example of doing a count, while the example below is like the max(case) example on the Intermediate SQL page but using Pivot instead:

SELECT course_name, S1, S2
FROM
(
SELECT course_name, grade, storecode
FROM storedgrades
WHERE studentid = 3  and storecode in ('S1','S2')
)
PIVOT
(max(grade) for storecode in ('S1' as S1,'S2' as S2))
ORDER BY course_name

You can pivot more than one item, such as grades and percents, by using additional aliases like so:

SELECT course_name,  S1_g, S1_p, S2_g, S2_p
FROM
(
SELECT course_name, grade, percent, storecode
FROM storedgrades
WHERE studentid = 3  and storecode in ('S1','S2')
)
PIVOT
(max(grade) as g, max(percent) as p for storecode in ('S1' as S1,'S2' as S2))
ORDER BY course_name

In the example above, both grade and percent are included in the inner select.  Then the pivot has an alias to each item in the max's - a g and a p - and then in the main select, those aliases are added to the other aliases, such as S1_g, S1_p, etc.  

Here's an example of a Pivot with a Group By Rollup, courtesy of Adam Larsen via the Yahoo PSUG group.  The columns are the grade levels and the count for each school and a final column with the row totals.  The rows are the individual grade levels and a final row with column totals:

SELECT grade_level, sum(S1), sum(S2), sum(S3), sum(S1+S2+S3)
FROM
(
SELECT grade_level, schoolid
FROM students
WHERE enroll_status = 0 and schoolid in (100,600,700)
)
PIVOT
(
count(*)
for schoolid in (100 as S1, 600 as S2, 700 as S3)

)
GROUP BY ROLLUP(grade_level)


Derived Tables

Derived Tables are a handy way to create temporary tables to use in a from clause by building another query that pulls data first.  They allow you to merge reports into one by adding different queries to one report, to get around long group by clauses, and are a handy way to find information for things like 'which students do not...'.  Here are some examples:

Maybe you want to find all the students who have F's in Historical Grades with a count of how many F's they have.  A basic statement would look something like this:

SELECT s.lastfirst, count(sg.grade)
FROM storedgrades sg
INNER JOIN students s on s.id = sg.studentid
WHERE sg.grade like 'F%'
GROUP BY sg.studentid, s.lastfirst
ORDER BY s.lastfirst

Since we're doing a count we have to group records together, in this case the studentid field.  However, we have to include any non-aggregate fields from the select clause in the group by.  Not bad when all we want is the name of the student, but let's say you want the name of the student, grade level, and home room.  In that case the following would work:

SELECT s.lastfirst, s.grade_level, s.home_room, count(sg.grade)
FROM storedgrades sg
INNER JOIN students s on s.id = sg.studentid
WHERE sg.grade like 'F%'
GROUP BY sg.studentid, s.lastfirst, s.grade_level, s.home_room
ORDER BY s.lastfirst

As we add more fields to the select clause we need to also add them to the group by.  The longer the group by becomes, the higher the chance the output could be wrong because of the grouping.  So the above could actually be written this way instead:

SELECT s.lastfirst, s.grade_level, s.home_room, sginfo.Fcount
FROM students s
INNER JOIN (SELECT studentid stuid, count(grade) Fcount
FROM storedgrades
WHERE grade like 'F%'
GROUP BY studentid) sginfo on sginfo.stuid = s.id
ORDER BY s.lastfirst

What the above does is make a temp table of data from the stored grades table and then that is joined to the students table.  The derived table is surrounded by parenthesis and given an alias.  Each item in it's select clause is given an alias and then any reference to that item in the main query is done by referencing the two aliases.  The derived tabled should include what will be used in the join, plus what will be used in the main query.  In the example above, the studentid (used in the join) and count (used in the main query) are referenced as sginfo.stuid and sginfo.Fcount.  Since the group by happens in the derived table, you don't need a group by in the main query.  So you could pull ten fields from the students table and never have to worry about including them in a group by.

Derived tables also come in handy in finding out which students are not part of the criteria.  The example above finds all the students who have an F, but with slight modifications - changing the inner join to a left outer join and adding a where - we can find those students who do not have any F's:

SELECT s.lastfirst, s.grade_level, s.home_room, sginfo.Fcount
FROM students s
LEFT OUTER JOIN (SELECT studentid stuid, count(grade) Fcount
FROM storedgrades
WHERE grade like 'F%'
GROUP BY studentid) sginfo on sginfo.stuid = s.id
WHERE sginfo.Fcount is Null
ORDER BY s.lastfirst

The left outer join will return all students.  Those students who have F's will have a number in the sginfo.Fcount column and those that don't will have a null value.  By adding the where clause, only those students with a null value - or no F's - will be returned.


With Clause

With's are similar to derived tables in that they create temporary tables of data, except the sql is defined before the main query.  Here is an example of how the F count from above would look using With instead:

WITH sginfo as
(SELECT studentid stuid, count(grade) Fcount
FROM storedgrades
WHERE grade like 'F%'
GROUP BY studentid)

SELECT s.lastfirst, s.grade_level, s.home_room, sginfo.Fcount
FROM students s
INNER JOIN sginfo on sginfo.stuid = s.id
ORDER BY s.lastfirst

Like a derived table, you need to include data that will be used in a join and in the select in the With query.  Alias everything and use those aliases in the main query.  You can have more than one With by separating them by commas like so:

WITH grdinfo as
(........),
   attinfo as
(........)

SELECT.......

FYI - if you wish to use sql with mulitple With's in sqlReports, you must be on version 4.3 or higher.

  Back to the List of Articles