Creating Reports from a Database

Output from a database is often called a report or an extract, which is information that has been queried in a specific way to answer a particular question (or set of questions).  You may have used these concepts while using Excel's Pivot Table function.  Below are some basic concepts for building most reports.

Aggregated Elements

Structured data can be rolled up (aggregated) in different ways, such as:
COUNT Count the number of a group of records
AVERAGEThe average of the records 
MAXThe highest number (or highest alpha) of a group of records 
MINThe lowest number (or lowest alpha) of a group of records

GROUP BY

GROUP BY means to set breaks in an aggregation, such as COUNT the number of records on this table, but return a value for both Male (M) and Female (F).  
Gendercount()
14 
Example query: SELECT Gender, COUNT() GROUP BY Gender


There can be multiple breaks, such as COUNT the number of records on this table but return values for Ethnicity and Gender, which might look something like this:
Ethnicity Gender count() 
Example query: SELECT Ethnicity, Gender, COUNT() GROUP BY Ethnicity, Gender

WHERE

Where means to filter the table to include only these elements, such as COUNT the number of records on this table, return a value for Ethnicity and Gender, but do not count include those who are marked as Male (M).

Ethnicity Gender count() 
Example query: SELECT Ethnicity, Gender, COUNT() WHERE Gender != 'F' GROUP BY Ethnicity, Gender


These ideas have been used to create the 2012 Title II Assessment Data Report. These concepts can be used in creating other tables, as well, including subsequent years' Title II assessment data reports, completer, and standards tables.