1

I'm running this script in MySQL to retrieve the count of appoinments that are grouped by a specific type and month:

SELECT Type, month(Start), COUNT(Type) FROM appointments GROUP BY Type, month(Start);

Which provides me with this table: [1]: https://i.stack.imgur.com/sRJwx.png

This is great! Now my problem is trying to place the data into variables:

String sql = "SELECT `Type`, month(`Start`), COUNT(Type) FROM appointments " +
                "GROUP BY `Type`, month(`Start`)";

        try(Statement s = DatabaseConnection.getConnection().createStatement();
        ResultSet rs = s.executeQuery(sql)) {
            while(rs.next()) {
                String typeKey = rs.getString("Type");
                int monthValue = rs.getInt("month(Start)");
                int count = rs.getInt("COUNT(Type)");

I get an SQLException when I run this, saying that the column "month(Type) cannot be found. I have tried with the column name "Start" and tried with just "month" and recieve the same SQLException (I know, kind of a stupid troubleshoot but worth a shot). I've looked around for some solutions but I'm falling short.

This is for a school project, so I can only use JDBC and Java specific drivers. I'd appreciate it if someone could point me in the right direction.

Stone
  • 21
  • 2
  • 5

1 Answers1

2

Try rename columns with as

sql = "SELECT `Type` as type ,
               month(`Start`) as mstart,
               COUNT(Type) as ctype
               FROM appointments " +
               "GROUP BY `Type`, month(`Start`)";
Traian GEICU
  • 1,750
  • 3
  • 14
  • 26
  • another option is to `getByIndex` , could look over https://stackoverflow.com/questions/186799/resultset-retrieving-column-values-by-index-versus-retrieving-by-label – Traian GEICU Aug 08 '21 at 01:29