javasqlselectgroup-byderby
How to select aggregated student / class data in Apache Derby?
I need to reproduce an access report in my Java app. Below is the access report to copy. The main concern is the group by contract number section and the visit / total time totals in the footer of each sub section.
I have spent many hours of playing with the SQL select statement and have yet to get it right.
My SQL statement is as follows:
select
max(all class_.class_number_) as "Contract Number",
max(all first_name_)|| ' ' || max(all last_name_) as "Student Name",
max(all class_.class_) as "Class",
max(all date(clock_in_time_)) as "Latest Date",
count( student_id_number_) as "visits",
Sum({fn timestampdiff(SQL_TSI_MINUTE,clock_in_time_, clock_out_time_)}) as "Total Time in Minutes"
from student_
join class_ on class_id_ = class_.id_
group by student_id_number_
My table schema is this: 
My query results are: 
So my question is how do I format my Java Derby select statement to separate the data by the contract number as a heading? I would love to also learn how to total Visits and Total time under each heading.
UPDATE
Brian's Suggestions produced these weird results do the aggregation of the names:
Blag was asking for the create table. Here it is. Sorry i forgot to add it.
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package timeclock;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
public class CreateDB {
public static final String DRIVER= "org.apache.derby.jdbc.EmbeddedDriver";
public static final String JDBC_URL = "jdbc:derby:student_;create=true";
public static void main(String[] args) throws ClassNotFoundException, SQLException
{
Class.forName(DRIVER);
isTableExist("STUDENT_");
}
public static boolean isTableExist(String sTablename) throws SQLException{
Connection connection = DriverManager.getConnection(JDBC_URL);
if(connection!=null)
{
DatabaseMetaData dbmd = connection.getMetaData();
ResultSet rs = dbmd.getTables(null, null, sTablename.toUpperCase(),null);
if(rs.next())
{
System.out.println("Table "+rs.getString("TABLE_NAME")+" already exists !!");
}
else
{
Timestamp timeIn = new Timestamp(System.currentTimeMillis());
Timestamp timeOut = new Timestamp(System.currentTimeMillis());
connection.createStatement().execute("create table STUDENT_(id_ INT not null primary key GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1), first_name_ varchar(20), last_name_ varchar(20), student_id_number_ varchar(6), class_id_ INT, clock_in_time_ timestamp, clock_out_time_ timestamp)");
System.out.println(" Students Table created");
connection.createStatement().execute("create table CLASS_(id_ INT not null primary key GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1), class_ varchar(20), class_number_ INT )");
System.out.println(" Class Table created");
System.out.println(timeIn);
String sql = "INSERT INTO STUDENT_ ( first_name_, last_name_, student_id_number_, class_id_, clock_in_time_, clock_out_time_ ) VALUES ( ?, ?, ?, ?, ?, ? ) " ;
PreparedStatement ps = connection.prepareStatement( sql );
ps.setString( 1, "Ben" );
ps.setString( 2, "Tester");
ps.setObject( 3, "649619");
ps.setObject( 4, "1");
ps.setObject( 5 , timeIn ) ;
ps.setObject( 6 , timeOut ) ;
ps.executeUpdate();
System.out.println(" Students record sucessfully inserted");
//This inserts class table
String sql2 = "INSERT INTO CLASS_ ( class_, class_number_) VALUES ( ?, ? ) " ;
PreparedStatement ps1 = connection.prepareStatement( sql2 );
ps1.setString( 1, "DBA");
ps1.setObject( 2, 123456);
ps1.executeUpdate();
connection.commit();
System.out.println(" Class record sucessfully inserted");
}
return true;
}
return false;
}
}
When a new student is created this is the class that runs
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package timeclock;
import java.awt.Container;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextField;
import javax.swing.SpringLayout;
import static timeclock.ClockIn.JDBC_URL;
/**
*
* @author jstil
*/
class CreateStudent extends JFrame implements ActionListener{
private static JFrame frame = new JFrame("Add Student");
private static JTextField firstEnter;
private static JTextField lastEnter;
private static JTextField idEnter;
private static JComboBox classEnter;
private JPanel panel;
public CreateStudent(String studentId){
JLabel AddLabel;
JLabel AddLabel2;
JLabel AddLabel3;
JLabel AddLabel4;
frame.setSize(500, 300);
frame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
// add instructions
AddLabel = new JLabel("Enter First Name :",JLabel.LEFT);
AddLabel2 = new JLabel("Enter Last Name :",JLabel.LEFT);
AddLabel3 = new JLabel("Enter your Student ID :", JLabel.LEFT);
AddLabel4 = new JLabel("Pick the class you are here for from the menu :",JLabel.LEFT);
firstEnter = new JTextField(20);
lastEnter = new JTextField(20);
idEnter = new JTextField(6);
classEnter = new JComboBox();
JButton submitButton = new JButton("Submit");
// font for Submit Buton
submitButton.setFont(new Font("Serif", Font.BOLD, 16));
submitButton.addActionListener(this);
// new panel creation be cause ContentPane panel = frame.getContentPane(); has been depreciated
panel = new JPanel();
//Set up Gui
SpringLayout layout = new SpringLayout();
panel.setLayout(layout);
layout.putConstraint(SpringLayout.WEST, AddLabel,
5,
SpringLayout.WEST, panel);
layout.putConstraint(SpringLayout.NORTH, AddLabel,
50,
SpringLayout.NORTH, panel);
//
layout.putConstraint(SpringLayout.WEST, firstEnter,
5,
SpringLayout.EAST, AddLabel);
layout.putConstraint(SpringLayout.NORTH, firstEnter,
50,
SpringLayout.NORTH, panel);
//
layout.putConstraint(SpringLayout.WEST, AddLabel2,
5,
SpringLayout.WEST, panel);
layout.putConstraint(SpringLayout.NORTH, AddLabel2,
90,
SpringLayout.NORTH, panel);
//
layout.putConstraint(SpringLayout.WEST, AddLabel3,
5,
SpringLayout.WEST, panel);
layout.putConstraint(SpringLayout.NORTH, AddLabel3,
130,
SpringLayout.NORTH, panel);
//
layout.putConstraint(SpringLayout.WEST, lastEnter,
5,
SpringLayout.EAST, AddLabel2);
layout.putConstraint(SpringLayout.NORTH, lastEnter,
90,
SpringLayout.NORTH, panel);
//
layout.putConstraint(SpringLayout.WEST, idEnter,
5,
SpringLayout.EAST, AddLabel3);
layout.putConstraint(SpringLayout.NORTH, idEnter,
130,
SpringLayout.NORTH, panel);
//
layout.putConstraint(SpringLayout.WEST, AddLabel4,
5,
SpringLayout.WEST, panel);
layout.putConstraint(SpringLayout.NORTH, AddLabel4,
180,
SpringLayout.NORTH, panel);
//
layout.putConstraint(SpringLayout.WEST, classEnter,
5,
SpringLayout.EAST, AddLabel4);
layout.putConstraint(SpringLayout.NORTH, classEnter,
180,
SpringLayout.NORTH, panel);
//
layout.putConstraint(SpringLayout.WEST, submitButton,
380,
SpringLayout.WEST, panel);
layout.putConstraint(SpringLayout.NORTH, submitButton,
210,
SpringLayout.NORTH, panel);
// add content to swing
panel.add(AddLabel);
panel.add(AddLabel2);
panel.add(AddLabel3);
panel.add(AddLabel4);
panel.add(firstEnter);
panel.add(lastEnter);
panel.add(idEnter);
panel.add(classEnter);
panel.add(submitButton);
//Display the window.
try {
ViewClass();
} catch (ClassNotFoundException | SQLException ex) {
Logger.getLogger(CreateStudent.class.getName()).log(Level.SEVERE, null, ex);
}
pack();
frame.setContentPane(panel);
frame.setVisible(true);
}
public void actionPerformed(ActionEvent evt)
{
String blank = ("");
String firstName = firstEnter.getText();
String lastName = lastEnter.getText();
String studentId =idEnter.getText();
String className = classEnter.getSelectedItem().toString();
String classId = null;
try {
classId = ClassIdSet(className);
} catch (SQLException ex) {
Logger.getLogger(CreateStudent.class.getName()).log(Level.SEVERE, null, ex);
}
int idOfClass = Integer.valueOf(classId);
if (firstName.equals(blank))
{
JOptionPane.showMessageDialog(null, "You forgot to add a First Name");
}
if (lastName.equals(blank))
{
JOptionPane.showMessageDialog(null, "You forgot to add a Last Name");
}
System.out.println(firstName + " and " + lastName + " recorded" );
try {
TimeStamp(firstName, lastName, studentId, idOfClass);
} catch (SQLException ex) {
Logger.getLogger(CreateStudent.class.getName()).log(Level.SEVERE, null, ex);
}
frame.dispose();
};
public static void ViewClass() throws ClassNotFoundException, SQLException
{
//This inserts class table
Connection connection3 = DriverManager.getConnection(JDBC_URL);
String sql3 = "SELECT class_ FROM CLASS_ ORDER BY class_";
try {
PreparedStatement ps2 = connection3.prepareStatement(sql3);
// execute select SQL stetement
ResultSet rs = ps2.executeQuery();
while (rs.next()) {
String existingClass = rs.getString("class_");
// add to scrollpanel
classEnter.addItem(existingClass);
System.out.println("classes loaded to combo box");
}
ps2.close();
connection3.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
System.out.println(" Class record sucessfully retrieved");
}
public static void TimeStamp(String first, String last, String number, Integer classId)throws SQLException
{
// create time stamp
Timestamp time = new Timestamp(System.currentTimeMillis());
// look up class id based on name
Connection connection2 = DriverManager.getConnection(JDBC_URL);
String sql2 = "INSERT INTO STUDENT_ ( first_name_, last_name_, student_id_number_, class_id_, clock_in_time_, clock_out_time_ ) VALUES ( ?, ?, ?, ?, ?,? ) " ;
PreparedStatement ps1 = connection2.prepareStatement( sql2 );
ps1.setString( 1, first );
ps1.setString( 2, last );
ps1.setString( 3, number );
ps1.setObject( 4, classId );
ps1.setObject( 5 , time ) ;
ps1.setObject( 6 , null ) ;
ps1.executeUpdate();
connection2.commit();
JOptionPane.showMessageDialog(null, "You are clocked in");
System.out.println(" Student sucessfully clocked in");
ps1.close();
connection2.close();
}
private String ClassIdSet(String name)throws SQLException
{
Connection connection = DriverManager.getConnection(JDBC_URL);
Statement stmt = connection.createStatement();
String sql2 = "select ID_ from class_ where class_ = ? ";
PreparedStatement ps1 = connection.prepareStatement( sql2 );
ps1.setString( 1, name);
ResultSet result = ps1.executeQuery();
if(!result.next()){
System.out.println("No Data Found"); //data not exist
return null;
}
else{
String classId= result.getString("ID_");
System.out.println("Class id is "+ classId );
return classId;
}
}
}
Solution
There are two issues here:
- The
student_table is storing data about students but also data about their class attendance. This is denormalized, meaning the names and student ID number will be repeated each time a student attends a class. Consider introducing astudent_class_mapping table instead to store class attendance data (i.e. links between students and classes with clock in/out times). - The footer data is aggregated for each class (contract) so it doesn't make sense to include the student names in that particular query.
Assuming no changes are made to the schema, you'll need two separate queries: One for the individual student data and the other for the footer data about each class.
Query #1 (student data)
SELECT class_.class_number_ AS "Contract Number",
first_name_ || ' ' || last_name_ AS "Student Name",
class_.class_ AS "Class",
MAX(ALL DATE(clock_in_time_)) AS "Latest Date",
COUNT(student_.id_) AS "visits",
SUM({FN TIMESTAMPDIFF(SQL_TSI_MINUTE, clock_in_time_, clock_out_time_)})
AS "Total Time in Minutes"
FROM student_
JOIN class_ ON class_id_ = class_.id_
GROUP BY class_.id_, class_.class_number_, class_.class_,
student_id_number_, first_name_, last_name_
Query #2 (aggregated footer data for each class)
SELECT class_.class_number_ AS "Contract Number",
COUNT(student_id_number_) AS "Total visits for this Contract Number",
SUM({FN TIMESTAMPDIFF(SQL_TSI_MINUTE, clock_in_time_, clock_out_time_)})
AS "Total time for this Contract Number"
FROM student_
JOIN class_ ON class_id_ = class_.id_
GROUP BY class_.class_number_
- 'wsimport' is not recognized error in command prompt
- Best way to compare two JSON files in Java
- Java get month sort name from date
- Obtain and download Javadoc (JDK API documentation) to a local file for offline reading
- How to get the number of days in a specific month using Java Calendar?
- Custom Spring annotation for request parameters
- License for package Android SDK Platform 29 not accepted
- Java Compile Time Error: reached end of file while parsing
- ShellIpcClient and NonCelloThread errors java
- How to verify a signature from the Phantom wallet?
- FirebaseAuth - Get tokenId in Java backend
- How to hide constructor on a Java record that offers a public static factory method?
- Is it possible to get MariaDB4J to work on an M1 Mac?
- Cannot run simple compiled java program?
- Getting IntelliJ to generate Java Sources from Proto files
- Insert a java string constant in a quarkus qute template?
- Why is the run button not working in Eclipse?
- Stuck on Card/Deck exercise from Java official tutorial
- Spring Batch - Deleting metadata post job completion throws error - Incorrect result size: expected 1, actual 0
- Simple export and import of a SQLite database on Android
- How to serialize a date to a specific format?
- How to make the Youtube's rotating spinner loading screen on Java Swing
- How to sort List<Integer[]> in java?
- How to prevent spring boot from auto creating instance of bean 'entityManagerFactory' at startup?
- Sharing instance of a class between multiple tests running in parallel in Junit5
- Launch4J not recognizing Eclipse Temurin OpenJDK Java 17
- Turn my stack into a string?
- How can I document or exclude the generated BuildConfig class in my documentation?
- Is it a bad practice to catch Throwable?
- Java: Right Click Copy Cut Paste On TextField