Cara menggunakan mysql count row

Question

Seperti kita ketahui, untuk menghitung row/baris pada tabel, kita menggunakan fungsi COUNT, yang otomatis menghitung jumlah row yang ada nilainya (row dengan nilai NULL tidak akan dihitung). baris yang dihitung dapat dilakukan dengan 2 cara yaitu:

Table of Contents Show

Introduction to the MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)1 function
INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)6 function
INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)7
INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)8
MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)1 function illustration
Setting up a sample table
MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)6 example
MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)7 example
MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)8 example
MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)1 function practical examples
A) Using MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)6 function with a SELECT COUNT(val) FROM count_demos;Code language: SQL (Structured Query Language) (sql)5 example
B) Using MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)6 with a SELECT COUNT(DISTINCT val) FROM count_demos;Code language: SQL (Structured Query Language) (sql)2 clause example
C) MySQL SELECT COUNT(DISTINCT val) FROM count_demos;Code language: SQL (Structured Query Language) (sql)5 example
Apa itu Count di mysql?
Apa itu sum dalam SQL?
Select distinct untuk apa?
Apa itu perintah select?

COUNT(*) yaitu menghitung row untuk semua field
COUNT(field_name) yaitu menghitung row untuk field tertentu

Sedangkan fungsi count dibag menjadi 2 yaitu:

COUNT untuk menghitung semua row walaupun ada duplikasi data
COUNT (DISTINCT) untuk menghitung row yang unique

melanjutkan pada artikel sebelumnya, sekarang kita akan menghitung jumlah item yang terjual berdasarkan tahun panjualan, query yang kita gunakan :

SELECT  COUNT(IF(tgl_byr LIKE "2011%", jml_byr, NULL)) AS item_2011,
        COUNT(IF(tgl_byr LIKE "2010%", jml_byr, NULL)) AS item_2010,
        SUM(IF(tgl_byr LIKE "2011%", jml_byr, 0)) AS jml_2011,
        SUM(IF(tgl_byr LIKE "2010%", jml_byr, 0)) AS jml_2010
FROM tunai

yang akan menghasilkan:

+----------+----------+----------+----------+
| item_2011| item_2010| jml_2011 | jml_2010 |
+----------+----------+----------+----------+
| 8        | 5        | 16124000 |  9955000 |
+----------+----------+----------+----------+

selanjutnya jika kita akan mengkelompokkan hasil tersebut berdasarkan field/kolom tertentu misal id_pelanggan dan id_cabang maka kita menggunakan GROUP BY. Contoh:

Summary: in this tutorial, you will learn how to use the MySQL

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

1 function to return the number rows in a table.

Introduction to the MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)1 function

The

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

1 function is an aggregate function that returns the number of rows in a table. The

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

1 function allows you to count all rows or only rows that match a specified condition.

The

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

1 function has three forms:

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

6,

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

7 and

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

8.

INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)6 function

The

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

6 function returns the number of rows in a result set returned by a

SELECT * FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

1 statement. The

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

6 returns the number of rows including duplicate, non-NULL and

SELECT * FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

3 rows.

INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)7

The

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

7 returns the number of rows that do not contain

SELECT * FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

3 values as the result of the expression.

INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)8

The

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

8 returns the number of distinct rows that do not contain

SELECT * FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

3 values as the result of the expression.

The return type of the

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

1 function is

SELECT COUNT(*) FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

1. The

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

1 function returns 0 if there is no matching row found.

MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)1 function illustration

Setting up a sample table

First, create a table called

SELECT COUNT(*) FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

4:

CREATE TABLE count_demos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    val INT
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the

SELECT COUNT(*) FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

4 table:

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

Third, query data from the

SELECT COUNT(*) FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

4 table:

SELECT * FROM count_demos;



Code language: SQL (Structured Query Language) (sql)

MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)6 example

The following statement uses the

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

6 function to return all rows from the

SELECT COUNT(*) FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

4 table:

SELECT COUNT(*) FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

This example uses the

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

6 function with a

SELECT COUNT(*) 
FROM count_demos
WHERE val = 2;
Code language: SQL (Structured Query Language) (sql)

1 clause to specify a condition to count only rows whose value in the column

SELECT COUNT(*) 
FROM count_demos
WHERE val = 2;
Code language: SQL (Structured Query Language) (sql)

2 is 2:

SELECT COUNT(*) 
FROM count_demos
WHERE val = 2;
Code language: SQL (Structured Query Language) (sql)

MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)7 example

If you specify the

SELECT COUNT(*) 
FROM count_demos
WHERE val = 2;
Code language: SQL (Structured Query Language) (sql)

2 column in the

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

1 function, the

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

1 function will count only rows with non-NULL values in the

SELECT COUNT(*) 
FROM count_demos
WHERE val = 2;
Code language: SQL (Structured Query Language) (sql)

2 column:

SELECT COUNT(val) 
FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

Notice that two

SELECT * FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

3 values are not included in the result.

MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)8 example

This example uses

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

8 to count non-NULL and distinct values in the column

SELECT COUNT(*) 
FROM count_demos
WHERE val = 2;



Code language: SQL (Structured Query Language) (sql)

2:

SELECT COUNT(DISTINCT val)
FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)1 function practical examples

We’ll use the

SELECT COUNT(val) 
FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

3 table from the sample database for the next examples:

A) Using MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)6 function with a SELECT COUNT(val) FROM count_demos;Code language: SQL (Structured Query Language) (sql)5 example

The

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

6 function is often used with a

SELECT COUNT(val) 
FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

7 clause to return the number of elements in each group.

For example, this statement uses the

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

1 function with the

SELECT COUNT(val) 
FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

5 clause to return the number of products in each product line:

SELECT 
    productLine, 
    COUNT(*)
FROM
    products
GROUP BY productLine;
Code language: SQL (Structured Query Language) (sql)

Similarly, this example uses the

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

6 function to find the number of products supplied by each vendor:

SELECT 
    productVendor, 
    COUNT(*)
FROM
    products
GROUP BY productVendor
ORDER BY COUNT(*) DESC;
Code language: SQL (Structured Query Language) (sql)

B) Using MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)6 with a SELECT COUNT(DISTINCT val) FROM count_demos;Code language: SQL (Structured Query Language) (sql)2 clause example

To find vendors who supply at least 9 products, you use the

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

6 function in the

SELECT COUNT(DISTINCT val)
FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

4 clause as shown in the following query:

SELECT 
    productVendor, 
    COUNT(*)
FROM
    products
GROUP BY productVendor
HAVING COUNT(*) >= 9
ORDER BY COUNT(*) DESC;
Code language: SQL (Structured Query Language) (sql)

C) MySQL SELECT COUNT(DISTINCT val) FROM count_demos;Code language: SQL (Structured Query Language) (sql)5 example

You can use a control flow expression and functions e.g.,

SELECT COUNT(DISTINCT val)
FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

6,

SELECT COUNT(DISTINCT val)
FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

7, and

SELECT COUNT(DISTINCT val)
FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

8 in the

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

1 function to count rows whose values match a condition.

See the following

SELECT 
    productLine, 
    COUNT(*)
FROM
    products
GROUP BY productLine;
Code language: SQL (Structured Query Language) (sql)

0 table from the sample database:

The following query use

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

1 with

SELECT 
    productLine, 
    COUNT(*)
FROM
    products
GROUP BY productLine;
Code language: SQL (Structured Query Language) (sql)

2 function to find the number of canceled, on hold and disputed orders from the

SELECT 
    productLine, 
    COUNT(*)
FROM
    products
GROUP BY productLine;
Code language: SQL (Structured Query Language) (sql)

0 table:

INSERT INTO count_demos(val) 
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Code language: SQL (Structured Query Language) (sql)

0

The

SELECT 
    productLine, 
    COUNT(*)
FROM
    products
GROUP BY productLine;
Code language: SQL (Structured Query Language) (sql)

4 function returns 1 if the order’s status is canceled, on hold or disputed, otherwise, it returns

SELECT * FROM count_demos;
Code language: SQL (Structured Query Language) (sql)

3.

The

SELECT 
    productLine, 
    COUNT(*)
FROM
    products
GROUP BY productLine;
Code language: SQL (Structured Query Language) (sql)

6 function only counts 1, not NULL values, therefore, the query returns the number of orders based on the corresponding status.

In this tutorial, you have learned various techniques to count the number of rows in a table using the MySQL

SELECT 
    productLine, 
    COUNT(*)
FROM
    products
GROUP BY productLine;
Code language: SQL (Structured Query Language) (sql)

6 function.

Apa itu Count di mysql?

SQL Count() digunakan untuk menampilkan banyaknya data (record) sesuai dengan kriteria tertentu.

Apa itu sum dalam SQL?

Fungsi sum() mengembalikan jumlah nilai numerik.

Select distinct untuk apa?

Fungsi Distinct mengevaluasi rumus di setiap rekaman tabel dan menghasilkan tabel satu kolom pada dengan nilai duplikat yang dihapus.

Apa itu perintah select?

Perintah SELECT adalah perintah yang digunakan untuk mengambil beberapa data yang dibutuhkan. Perintah ini merupakan tipe sintak DML, perintah ini pun digunakan sebagai sebagai perintah dasar untuk memilah informasi dari database.

kode mysql

Cara menggunakan mysql count row

Introduction to the MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)1 function

INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)6 function

INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)7

INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)8

MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)1 function illustration

Setting up a sample table

MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)6 example

MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)7 example

MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)8 example

MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)1 function practical examples

A) Using MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)6 function with a SELECT COUNT(val) FROM count_demos;Code language: SQL (Structured Query Language) (sql)5 example

B) Using MySQL INSERT INTO count_demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);Code language: SQL (Structured Query Language) (sql)6 with a SELECT COUNT(DISTINCT val) FROM count_demos;Code language: SQL (Structured Query Language) (sql)2 clause example

C) MySQL SELECT COUNT(DISTINCT val) FROM count_demos;Code language: SQL (Structured Query Language) (sql)5 example

Apa itu Count di mysql?

Apa itu sum dalam SQL?

Select distinct untuk apa?

Apa itu perintah select?

Pos Terkait

Periklanan

BERITA TERKINI

Toplist Popular

Periklanan

Terpopuler

Token Data

Periklanan

Tentang Kami

Legal

Dukungan

Social