Sql Count Multiple Column Group by and Where Clause
MySQL COUNT() function with group by
COUNT() function with group by
In this page we have discussed how to use MySQL COUNT() function with GROUP BY.
Example:
The following MySQL statement will show number of author for each country. The GROUP BY clause groups all records for each country and then COUNT() function in conjunction with GROUP BY counts the number of authors for each country.
Sample table: author
Code:
SELECT country,COUNT(*) FROM author GROUP BY country; Sample Output:
mysql> SELECT country, COUNT(*) -> FROM author -> GROUP BY country; +-----------+----------+ | country | COUNT(*) | +-----------+----------+ | Australia | 2 | | Brazil | 1 | | Canada | 2 | | Germany | 1 | | India | 1 | | UK | 4 | | USA | 4 | +-----------+----------+ 7 rows in set (0.00 sec)
Pictorial Presentation
PHP script
<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>example-aggregate-functions-and-grouping-count-with-group-by- php mysql examples | w3resource</title> <meta name="description" content="example-aggregate-functions-and-grouping-count-with-group-by- php mysql examples | w3resource"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css"> </head> <body> <div class="container"> <div class="row"> <div class="col-md-12"> <h2>Taking an account of how many authors are there in different countries (in author table):</h2> <table class='table table-bordered'> <tr> <th>Country</th><th>Number of authors</th> </tr> <?php $hostname="your_hostname"; $username="your_username"; $password="your_password"; $db = "your_dbname"; $dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password); foreach($dbh->query('SELECT country,COUNT(*) FROM author GROUP BY country') as $row) { echo "<tr>"; echo "<td>" . $row['country'] . "</td>"; echo "<td>" . $row['COUNT(*)'] . "</td>"; echo "</tr>"; } ?> </tbody></table> </div> </div> </div> </body> </html> View the example in browser
JSP Script
<%@page contentType="text/html" pageEncoding="UTF-8"%> <%@ page import="java.sql.*" %> <%@ page import="java.io.*" %> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>example-aggregate-functions-and-grouping-count-with-group-by</title> </head> <body> <% try { Class.forName("com.mysql.jdbc.Driver").newInstance(); String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo"; Connection connection = null; Statement statement = null; ResultSet rs = null; connection = DriverManager.getConnection(Host, "root", "datasoft123"); statement = connection.createStatement(); String Data = "SELECT country,COUNT(*)FROM author GROUP BY country"; rs = statement.executeQuery(Data); %> <TABLE border="1"> <tr width="10" bgcolor="#9979"> <td>Country</td> <td>Number of authors</td> </tr> <% while (rs.next()) { %> <tr> <td><%=rs.getString("country")%></td> <td><%=rs.getString("COUNT(*)")%></td> </tr> <% } %> </table> <% rs.close(); statement.close(); connection.close(); } catch (Exception ex) { out.println("Can't connect to database."); } %> </body> </html> MySQL COUNT() function with group by on multiple columns
The following MySQL statement returns number of publishers in each city for a country. Grouping operation is performed on country and pub_city column with the use of GROUP BY and then COUNT() counts the number of publishers for each groups.
Sample table: publisher
Code:
SELECT country,pub_city,COUNT(*) FROM publisher GROUP BY country,pub_city; Sample Output:
mysql> SELECT country,pub_city,COUNT(*) -> FROM publisher -> GROUP BY country,pub_city; +-----------+-----------+----------+ | country | pub_city | COUNT(*) | +-----------+-----------+----------+ | Australia | Adelaide | 1 | | India | Mumbai | 1 | | India | New Delhi | 1 | | UK | Cambridge | 1 | | UK | London | 1 | | USA | Houstan | 1 | | USA | New York | 2 | +-----------+-----------+----------+ 7 rows in set (0.00 sec)
PHP script
<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>example-count-with-group-by-on-multiple-columns- php mysql examples | w3resource</title> <meta name="description" content="example-count-with-group-by-on-multiple-columns- php mysql examples | w3resource"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css"> </head> <body> <div class="container"> <div class="row"> <div class="col-md-12"> <h2>Taking an account of how many authors are there with respect to countries and cities (in author table):</h2> <table class='table table-bordered'> <tr> <th>Country</th><th>City</th><th>Number of authors</th> </tr> <?php $hostname="your_hostname"; $username="your_username"; $password="your_password"; $db = "your_dbname"; $dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password); foreach($dbh->query('SELECT country,pub_city,COUNT(*) FROM publisher GROUP BY country,pub_city') as $row) { echo "<tr>"; echo "<td>" . $row['country'] . "</td>"; echo "<td>" . $row['pub_city'] . "</td>"; echo "<td>" . $row['COUNT(*)'] . "</td>"; echo "</tr>"; } ?> </tbody></table> </div> </div> </div> </body> </html> View the example in browser
JSP script
<%@page contentType="text/html" pageEncoding="UTF-8"%> <%@ page import="java.sql.*" %> <%@ page import="java.io.*" %> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>example-aggregate-functions-and-grouping-count-with-group-by</title> </head> <body> <% try { Class.forName("com.mysql.jdbc.Driver").newInstance(); String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo"; Connection connection = null; Statement statement = null; ResultSet rs = null; connection = DriverManager.getConnection(Host, "root", "datasoft123"); statement = connection.createStatement(); String Data = "SELECT country,COUNT(*)FROM author GROUP BY country"; rs = statement.executeQuery(Data); %> <TABLE border="1"> <tr width="10" bgcolor="#9979"> <td>Country</td> <td>Number of authors</td> </tr> <% while (rs.next()) { %> <tr> <td><%=rs.getString("country")%></td> <td><%=rs.getString("COUNT(*)")%></td> </tr> <% } %> </table> <% rs.close(); statement.close(); connection.close(); } catch (Exception ex) { out.println("Can't connect to database."); } %> </body> </html> Previous: COUNT()
Next: COUNT(DISTINCT)
Sql Count Multiple Column Group by and Where Clause
Source: https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-count-with-group-by.php
Belum ada Komentar untuk "Sql Count Multiple Column Group by and Where Clause"
Posting Komentar