The user defined group interface allows users to specify conditions that make up the WHERE clause of a query. The WHERE clause is used to specify that only certain rows of a table are displayed, based on the criteria described in that WHERE clause. To review a WHERE clause at anytime click Review group/query as statement. This will display the WHERE clause you have generated in both plain English and SQL (Structured Query Language).
When generating user defined groups it is possible to specify up to 10 conditions. A condition can be applied against a value or another field.
WHERE ICT_Y8_NCL equals Value: 5a
Finds all the pupils whose Y8_NCL score equaled 5a
WHERE S2_BGE_Level_May equals Field: S2_BGE_Level_Nov
Finds all the pupils whose S2_BGE_Level_May score was the same as their S2_BGE_Level_Nov score.
Selecting a Value or Another Field
When selecting a field the Value or another Field column will refresh. The contents of the refreshed field will differ depending on whether the user is performing a subject (when using View or Compare) or administrative task (when using Query Manager). The refreshed column will also differ depending on the condition selected.
If the field ICT_Y7_NCL is selected along with the equals condition then the Value or another Field column will be refreshed with the distinct contents of that field along with the other fields available for use by that department.
Value: (represents an empty string "" (Text), 0 (Whole number) or 0.0 (Decimal)
Value: NULL (see Working with NULL Values)
The distinct contents of the field ICT_Y7_NCL represents the only values currently available in this field. To select any other value would result in an empty results set. However, to manually type a value users can select Other: This will change the select box to a text box where a value can be entered. The following conditions also result in a text box being displayed because they are all string related conditions.
Does not begin with
Does not end with
Does not contain
If the field Shared: S3_Milestone_Nov is selected along with the equals condition then the Value or another Field column will be refreshed with the contents of the select box that has been created for this field. If no select box has been created then no values will be displayed and Other: must be selected to allow the manual entry of a value.
Important Differences Between Academic Values and Values in SQL
In order to get the most from the user defined group interface it is important to understand some fundamental differences between the way academic values are used and the way SQL (or any spreadsheet/database application) understands them. In SQL A is worth less than F. This is different from the world of academia where a grade A is worth more than a grade F. This is easily dealt with providing that you bear this in mind when specifying conditions for your user defined group.
Using legacy GCSE grades as an example:
WHERE ICT_Y11_Predicted_GCSE is less than Value: C
Finds all the pupils whose ICT_Y11_Predicted_GCSE grade is either A,A* or B
WHERE ICT_Y11_Predicted_GCSE is greater than Value: C
Finds all the pupils whose ICT_Y11_Predicted_GCSE grade is either D,E,F or G
Numeric values can obviously be dealt with as expected.
Constructing Complex Queries
Sometimes it maybe necessary to use multiple conditions to achieve the desired results.
You want to find all the Males on the ASN register who have a CAT Average score of less than 112 and whose S3_BGE_Level_Nov is less than their S3_Target_BGE_Level. Use the following query:
WHERE Gender equals Value: M
AND ASN equals Value: Y
AND CATavg is less than Value: 112
AND S3_BGE_Level_Nov is less than Field: S3_Target_BGE_Level
When to use OR
Use OR when you want to use the same field more than once in the same query.
To find all the pupils in year 7 or in year 8:
WHERE ( Year = Value:7
OR Year = Value: 8 )
To find all the males in year 7 or in year 8:
WHERE Gender = Value: M AND(
Year = Value: 7 OR Year = Value: 8 )
Using AND in the above situations would result in an empty results set because there are no pupils in year 7 AND year 8.
Important: To avoid slow queries and unexpected results always surround OR statements with brackets.
Working with NULL Values
The concept of the NULL value is a common source of confusion for newcomers to SQL (Structured Query Language), who often think that NULL is the same as an empty string "" (blank). This is not the case. A NULL value is neither an empty string "" (Text), 0 (Whole number) or 0.0 (Decimal). A NULL value is best thought of as contents of a record before any value (even an empty string "") was saved to the database.
Pupil Tracking stores empty strings "" as NULL values. This allows empty strings "" entered into whole number or decimal fields to appear blank rather than 0 or 0.0.
Consider the following queries:
To find all the pupils who scored 0 in a recent test.
WHERE TEST_Result equals Value:
To find all the pupils who have not had any data entered for them in a recent test
WHERE TEST_Result equals Value: NULL
To find all the pupils who scored 0 in a recent test or who have not yet had data entered for them
WHERE ( TEST_Result equals Value: OR TEST_Result equals Value: NULL)
NULL values do not physically show up in Pupil Tracking, so if constructing a query to find blanks be sure to also include a clause to look for NULL values as well as empty strings.