MySQL - GROUP BY 子句

要在 JavaScript 程式中透過 MySQL 表使用 GROUP BY 子句，我們需要使用 mysql2 聯結器提供的名為 query() 的函式執行 SQL 語句。

sql= " SELECT column_name(s) FROM table_name
   GROUP BY [condition | column_name(s)];"
Con.query(sql);

要在 Java 程式中透過 MySQL 表使用 GROUP BY 子句，我們需要使用 JDBC type 4 驅動程式提供的名為 executeQuery() 的函式執行 SQL 語句。

String sql = "SELECT column_name(s) FROM table_name
   GROUP BY [condition | column_name(s)]";
statement.executeQuery(sql);

要在 Java 程式中透過 MySQL 表使用 GROUP BY 子句，我們需要使用 MySQL Connector/Python 提供的名為 execute() 的函式執行 SQL 語句。

group_by_clause_query = "SELECT column_name(s) aggregate_function(column)
   FROM table_name GROUP BY column_name(s)"
cursorObj.execute(group_by_clause_query)

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "CREATE TABLE IF NOT EXISTS employee_tbl(id INT NOT NULL, name VARCHAR(100) NOT NULL, work_date DATE, daily_typing_pages INT);"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO employee_tbl(id, name, work_date, daily_typing_pages) VALUES(1, 'John', '2007-01-24', 250), (2, 'Ram', '2007-05-27', 220), (3, 'Jack', '2007-05-06', 170), (3, 'Jack', '2007-04-06', 100), (4, 'Jill', '2007-04-06', 220),(5, 'Zara', '2007-06-06', 300),(5, 'Zara', '2007-02-06', 350);"
  con.query(sql);

  //Using GROUP BY Clause
  sql = "SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

輸出

產生的輸出如下所示：

Connected!
--------------------------
[
  { name: 'John', 'COUNT(*)': 1 },
  { name: 'Ram', 'COUNT(*)': 1 },
  { name: 'Jack', 'COUNT(*)': 2 },
  { name: 'Jill', 'COUNT(*)': 1 },
  { name: 'Zara', 'COUNT(*)': 2 }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class GroupByClause {
	public static void main(String[] args) {
		String url = "jdbc:mysql://:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT AGE, AVG(SALARY) as avg_salary FROM CUSTOMERS GROUP BY age";
            rs = st.executeQuery(sql);
            System.out.println("Table records(gruop by age): ");
            while(rs.next()){
            	String age = rs.getString("Age");
            	String avg_salary = rs.getString("avg_salary");
            	System.out.println("Age: " + age + ", Salary: " + avg_salary);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

輸出

獲得的輸出如下所示：

Table records(gruop by age): 
Age: 32, Salary: 2000.000000
Age: 25, Salary: 4000.000000
Age: 23, Salary: 2000.000000
Age: 27, Salary: 8500.000000
Age: 22, Salary: 4500.000000
Age: 24, Salary: 10000.000000

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object 
cursorObj = connection.cursor()
group_by_clause_query = """
SELECT ADDRESS, AVG(SALARY) AS average_salary
FROM customers
GROUP BY ADDRESS
"""
cursorObj.execute(group_by_clause_query)
# Fetching all the grouped rows
grouped_rows = cursorObj.fetchall()
for row in grouped_rows:
    address, average_salary = row
    print(f"Address: {address}, Average Salary: {average_salary}")
cursorObj.close()
connection.close()

輸出

以下是上述程式碼的輸出：

Address: Hyderabad, Average Salary: 7500.000000
Address: Kerala, Average Salary: 8000.000000
Address: Mumbai, Average Salary: 1200.000000
Address: Vishakapatnam, Average Salary: 8500.000000
Address: Delhi, Average Salary: 10000.000000