SQLlite:グループ化

検索対象データベース

集計関数
MAX　最大値
MIN　最小値
AVG　平均値
COUNT　データの個数

合計(Sum)
name別にpriceの合計を計算して表示

ResultSet rs = stmt.executeQuery("SELECT name,Sum(name) FROM data GROUP BY name "); 

while (rs.next()) {
 System.out.println("name = " + rs.getString("name"));
 System.out.println("price = " + rs.getInt(2));
}

結果：
name = いちご
price = 110
name = りんご
price = 201

データの個数(Count)

ResultSet rs = stmt.executeQuery("SELECT name,Count(name) FROM data GROUP BY name ");

while (rs.next()) {
 System.out.println("name = " + rs.getString("name"));
 System.out.println("count = " + rs.getInt(2));
}

結果：
name = いちご
count= 1
name = りんご
count= 2


複数の集計関数を指定

ResultSet rs = stmt.executeQuery("SELECT name,Sum(price),Avg(price) FROM data GROUP BY name ");

while (rs.next()) {
 System.out.println("name = " + rs.getString("name"));
 System.out.println("合計 = " + rs.getInt(2));
 System.out.println("平均 = " + rs.getFloat(3));
}

結果：
name = いちご
合計 = 110
平均 = 110.0
name = りんご
合計 = 201
平均 = 100.5

列名の指定(AS)

ResultSet rs = stmt.executeQuery("SELECT name,Sum(price) AS '合計' FROM data GROUP BY name ");

while (rs.next()) {
 System.out.println("name = " + rs.getString("name"));
 System.out.println("合計 = " + rs.getInt("合計"));
}

特定の集計行のみの表示(HAVING)

ResultSet rs = stmt.executeQuery("SELECT name,Sum(price) AS '合計' FROM data GROUP BY name HAVING Sum(price) > 200 ");

while (rs.next()) {
 System.out.println("name = " + rs.getString("name"));
 System.out.println("合計 = " + rs.getInt("合計"));
}

結果：
name = りんご
合計 = 201