توابع تجمیعی (Aggregate Functions) در MySQL

کاربرد توابع تجمیعی (Aggregate Functions)

داشتن دورنمای کلی و خلاصه ای از مقادیر عددی ردیف های جداول پایگاه داده به طور مثال جهت نمایش بر روی نمودارها و ارائه تجزیه تحلیل های آماری به خصوص در برنامه ها و پروژه هایی که با حجم انبوهی از اطلاعات سر و کار دارند از جمله دلایلی است که لزوم استفاده از توابع انجام محاسبات روی گروهی از اطلاعات را ایجاب می کنند، با کمک توابع تجمیعی (Aggregate Functions) می توانیم تعداد، بیشترین، کمترین، میانگین و مجموع مقادیر ردیف ها را در میان گروهی از اطلاعات محاسبه کرده و نتیجه را به شکل مقدار واحد استخراج کنیم، برای شروع آموزش این مبحث به پایگاه داده، جدول و نمونه اطلاعاتی به شرح زیر نیازمندیم.

ساخت دیتابیس، جدول و ورود نمونه اطلاعات

قبل از پرداختن موردی به توابع تجمیعی ابتدا با استفاده از برنامه phpMyAdmin یک دیتابیس با نام دلخواه (ترجیحا Collation به صورت utf8_persian_ci) و جدولی فرضی با نام tbl_cars با نمونه ردیف های زیر در آن ایجاد می کنیم:

id  =>  int(11) AUTO_INCREMENT
name  =>  varchar(255)
model  =>  varchar(255)
color  =>  varchar(255)
price  =>  int(11)

هم به صورت دستی و هم با اجرای دستورات زیر در قسمت SQL برنامه phpMyAdmin می توانیم جدول بالا را ایجاد کنیم:

CREATE TABLE IF NOT EXISTS `tbl_cars` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL,
  `model` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL,
  `color` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL,  
  `price` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `model` (`model`),
  KEY `color` (`color`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci

نکته مهم: با توجه به اینکه ممکن است در آینده حروف زبان فارسی را در دیتابیس ذخیره یا از آن دریافت کنیم توصیه می شود Collation دیتابیس و جدول را در برنامه phpMyAdmin به صورت utf8_persian_ci تنظیم کنیم.
برای اینکه نمونه اطلاعاتی جهت تست و بررسی در اختیار داشته باشیم دستورات زیر را در قسمت SQL برنامه phpMyAdmin برای جدول tbl_cars اجرا می کنیم:

INSERT INTO `tbl_cars` (`id`, `name`, `model`, `color`, `price`) VALUES
(1, 'Peugeot', 1399, "black", 1500000),
(2, 'Pride', 1398, "white", 1000000),
(3, 'Nissan', 1400, "blue", 1200000),
(4, 'Tiba', 1400, "white", 1100000),
(5, 'Mazda', 1395, "silver", 1300000),
(6, 'Dena', 1399, "black", 1400000);

یا اینکه می توانیم کدهای زیر را در یک فایل PHP درج و پس از اعمال تنظیمات اتصال در متغیر conn از طریق مرورگر فراخوانی کنیم.
برنامه نویسی رویه ای (Procedural):

<?php
$array = array(
    'name' => array('Peugeot', 'Pride', 'Nissan', 'Tiba', 'Mazda', 'Dena'),
    'model' => array(1399, 1398, 1400, 1400, 1395, 1399),
    'color' => array('black', 'white', 'blue', 'white', 'silver', 'black'),
    'price' => array(1500000, 1000000, 1200000, 1100000, 1300000, 1400000)
);
$count = count($array['name']);
//اتصال به دیتابیس
$conn = mysqli_connect("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . mysqli_connect_errno() . ' - ' . mysqli_connect_error();
    exit;
} else {
    //ذخیره نمونه اطلاعات در جدول
    for($i = 0; $i < $count; $i++){
        $sql = "INSERT INTO tbl_cars(name, model, color, price) VALUES('" . $array['name'][$i] . "', '" . $array['model'][$i] . "', '" . $array['color'][$i] . "', '" . $array['price'][$i] . "')";
        $query = mysqli_query($conn, $sql);
         
        if(!$query) {
            echo "Inserting Into Table tbl_cars: Error! " . mysqli_error($conn);
            break;
        } else {
            echo "tbl_cars: Row " . ($i + 1) . " Inserted!<br>";
        }
    }
}
//پایان اتصال
mysqli_close($conn);
?>

برنامه نویسی شی گرا (Object-oriented):

<?php
$array = array(
    'name' => array('Peugeot', 'Pride', 'Nissan', 'Tiba', 'Mazda', 'Dena'),
    'model' => array(1399, 1398, 1400, 1400, 1395, 1399),
    'color' => array('black', 'white', 'blue', 'white', 'silver', 'black'),
    'price' => array(1500000, 1000000, 1200000, 1100000, 1300000, 1400000)
);
$count = count($array['name']);
//اتصال به دیتابیس
$conn = new mysqli("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . $conn->errno . ' - ' . $conn->error;
    exit;
} else {
    //ذخیره نمونه اطلاعات در جدول
    for($i = 0; $i < $count; $i++){
        $sql = "INSERT INTO tbl_cars(name, model, color, price) VALUES('" . $array['name'][$i] . "', '" . $array['model'][$i] . "', '" . $array['color'][$i] . "', '" . $array['price'][$i] . "')";
        $query = $conn->query($sql);
         
        if(!$query) {
            echo "Inserting Into Table tbl_cars: Error! " . $conn->error;
            break;
        } else {
            echo "tbl_cars: Row " . ($i + 1) . " Inserted!<br>";
        }
    }
}
//پایان اتصال
$conn->close();
?>

تابع تجمیعی COUNT، شمارش تعداد ردیف ها

یکی از پرکاربردترین توابع تجمیعی در پرس و جوهای MySQL تابع COUNT است که برای شمارش تعداد ردیف ها در یکی  از شرایط زیر استفاده می شود:
- استفاده از COUNT به همراه علامت *، در این حالت تمام ردیف های انتخاب شده در پرس و جوی SELECT (شامل مقادیر NULL و تکراری) شمارش می شوند، به فرض:

SELECT COUNT(*) FROM tbl_cars WHERE model > 1350

- استفاده از COUNT و شمارش ستون خاص به طور مثال id، در این حالت شمارش شامل مقادیر NULL برای ستون تعیین شده نمی شود به فرض:

SELECT COUNT(id) FROM tbl_cars WHERE model > 1350

نکته: نمونه اطلاعاتی که قبلا درج کردیم در هیچ ردیفی شامل مقدار NULL نمی شوند و پرس و جوهای بالا نتیجه یکسان خواهند داشت اما در صورتی که به هر دلیل مقدار یک یا چند ردیف NULL باشد نتیجه متفاوت خواهد بود.
- استفاده از COUNT و DISTINCT برای به دست آوردن تعداد مقادیر غیرتکراری (متفاوت) در پرس و جو، به فرض:

SELECT COUNT(DISTINCT color) FROM tbl_cars WHERE model > 1350

برای تست و بررسی تابع COUNT کافی است پرس و جوهای بالا را در قسمت SQL برنامه phpMyAdmin اجرا یا کد زیر را در یک فایل PHP درج و پس از اعمال تنظیمات اتصال در متغیر conn از طریق مرورگر فراخوانی کنیم.
برنامه نویسی رویه ای (Procedural):

<?php
//اتصال به دیتابیس
$conn = mysqli_connect("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . mysqli_connect_errno() . ' - ' . mysqli_connect_error();
    exit;
} else{    
    //سازگاری با حروف فارسی
    $sql = "SET NAMES 'utf8'";
    $query = mysqli_query($conn, $sql);
    
    //اانتخاب اطلاعات از جدول و ستون ها
    $sql = "SELECT name, COUNT(id) FROM tbl_cars WHERE name != ''";
    $query = mysqli_query($conn, $sql);
    
    if(!$query){
        echo "Selecting From Table tbl_cars: Error! " . mysqli_error($conn) . '<br>';
    } else{
        //تعداد ردیف های انتخاب شده
        $count = mysqli_num_rows($query);
        $loop = 1;
        
        //پردازش و خروجی ردیف های اطلاعات
        if($count > 0){
            echo '<div style="direction: ltr; border: 1px solid #999;">';
            
            echo 'Row Count: ' . $count . '<br><br>';
            while($row = mysqli_fetch_array($query)){
                echo 'Row ' . $loop . ' => ' . 
                'Name: ' . $row['name'] . ' - ' . 
                'Count: ' . $row['COUNT(id)'] . '<br><br>';
                
                $loop++;
            }
            
            echo '</div>';
        } else{
            echo "No Rows Found in Table tbl_cars!";
        }
    }
}
//پایان اتصال
mysqli_close($conn);
?>

برنامه نویسی شی گرا (Object-oriented):

<?php
//اتصال به دیتابیس
$conn = new mysqli("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . $conn->errno . ' - ' . $conn->error;
    exit;
} else {
    //سازگاری با حروف فارسی
    $sql = "SET NAMES 'utf8'";
    $conn->query($sql);
    
    //اانتخاب اطلاعات از جدول و ستون ها
    $sql = "SELECT name, COUNT(id) FROM tbl_cars WHERE name != ''";
    $query = $conn->query($sql);
    
    //دریافت اطلاعات از جدول
    if(!$query) {
        echo "Selecting From Table tbl_cars: Error! " . $conn->error . '<br>';
    } else {
        //تعداد ردیف های انتخاب شده
        $count = $query->num_rows;
        $loop = 1;
        
        //پردازش و خروجی ردیف های اطلاعات
        if($count > 0){
            echo '<div style="direction: ltr; border: 1px solid #999;">';
            
            echo 'Row Count: ' . $count . '<br><br>';
            while($row = $query->fetch_array()){
                echo 'Row ' . $loop . ' => ' . 
                'Name: ' . $row['name'] . ' - ' . 
                'Count: ' . $row['COUNT(id)'] . '<br><br>';
                
                $loop++;
            }
            
            echo '</div>';
        } else{
            echo "No Rows Found in Table tbl_cars!";
        }
    }
}
//پایان اتصال
$conn->close();
?>

خروجی نمونه دستورات بالا به صورت زیر خواهد بود:

Row Count: 1
Row 1 => Name: Peugeot - Count: 6

نکته جالب اینکه زمانی که در پرس و جو از COUNT استفاده می کنیم تنها یک ردیف اطلاعات در خروجی قابل دریافت است اما شمارش ردیف id مقادیر 6 را برمی گرداند، این حالت به دلیل تجمیعی بودن تابع COUNT است که به نوعی ابتدا به صورت پیش فرض GROUP BY را بر روی ردیف ها اعمال می کند، اگر به هر دلیل همزمان نیاز به شمارش کل و دریافت مقادیر ردیف ها در یک پرس و جو داشتیم می توانیم قسمت SQL را به شکل نمونه زیر بازنویسی کنیم:

SELECT name, (SELECT COUNT(id) FROM tbl_cars WHERE name != '') AS count_id FROM tbl_cars WHERE name != ''

همچنین در قسمت دریافت و چاپ نتایج در حلقه while به جای حالت بالا برای نام ستون از کلید count_id در آرایه row استفاده می کنیم:

while($row = mysqli_fetch_array($query)){
    echo 'Row ' . $loop . ' => ' .
    'Name: ' . $row['name'] . ' - ' .
    'Count: ' . $row['count_id'] . '<br><br>';
    
    $loop++;
}

که خروج به شکل زیر خواهد بود:

Row Count: 6
Row 1 => Name: Dena - Count: 6
Row 2 => Name: Mazda - Count: 6
Row 3 => Name: Nissan - Count: 6
Row 4 => Name: Peugeot - Count: 6
Row 5 => Name: Pride - Count: 6
Row 6 => Name: Tiba - Count: 6

به این صورت علاوه بر شمارش تعداد کل ردیف های مد نظر در قسمت COUNT، مقادیر سایر ردیف ها نیز قابل دسترسی است.

تابع تجمیعی MIN، انتخاب کمترین مقدار

با تابع MIN می توانیم کمترین مقدار را از بین نتایج پرس و جو استخراج کنیم، در استفاده از تابع تجمیعی MIN باید ستون مورد نظر را تعیین کنیم، نوع ستون باید عددی باشد به فرض:

SELECT MIN(price) FROM tbl_cars WHERE id != 0

برای تست و بررسی تابع MIN کافی است پرس و جوی بالا را در قسمت SQL برنامه phpMyAdmin اجرا یا کد زیر را در یک فایل PHP درج و پس از اعمال تنظیمات اتصال در متغیر conn از طریق مرورگر فراخوانی کنیم.
برنامه نویسی رویه ای (Procedural):

<?php
//اتصال به دیتابیس
$conn = mysqli_connect("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . mysqli_connect_errno() . ' - ' . mysqli_connect_error();
    exit;
} else{    
    //سازگاری با حروف فارسی
    $sql = "SET NAMES 'utf8'";
    $query = mysqli_query($conn, $sql);
    
    //اانتخاب اطلاعات از جدول و ستون ها
    $sql = "SELECT MIN(price) FROM tbl_cars WHERE id != 0";
    $query = mysqli_query($conn, $sql);
    
    if(!$query){
        echo "Selecting From Table tbl_cars: Error! " . mysqli_error($conn) . '<br>';
    } else{
        //تعداد ردیف های انتخاب شده
        $count = mysqli_num_rows($query);
        $loop = 1;
        
        //پردازش و خروجی ردیف های اطلاعات
        if($count > 0){
            echo '<div style="direction: ltr; border: 1px solid #999;">';
            
            echo 'Row Count: ' . $count . '<br><br>';
            while($row = mysqli_fetch_array($query)){
                echo 'Row ' . $loop . ' => ' . 
                'Min: ' . $row['MIN(price)'] . '<br><br>';
                
                $loop++;
            }
            
            echo '</div>';
        } else{
            echo "No Rows Found in Table tbl_cars!";
        }
    }
}
//پایان اتصال
mysqli_close($conn);
?>

برنامه نویسی شی گرا (Object-oriented):

<?php
//اتصال به دیتابیس
$conn = new mysqli("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . $conn->errno . ' - ' . $conn->error;
    exit;
} else {
    //سازگاری با حروف فارسی
    $sql = "SET NAMES 'utf8'";
    $conn->query($sql);
    
    //اانتخاب اطلاعات از جدول و ستون ها
    $sql = "SELECT MIN(price) FROM tbl_cars WHERE id != 0";
    $query = $conn->query($sql);
    
    //دریافت اطلاعات از جدول
    if(!$query) {
        echo "Selecting From Table tbl_cars: Error! " . $conn->error . '<br>';
    } else {
        //تعداد ردیف های انتخاب شده
        $count = $query->num_rows;
        $loop = 1;
        
        //پردازش و خروجی ردیف های اطلاعات
        if($count > 0){
            echo '<div style="direction: ltr; border: 1px solid #999;">';
            
            echo 'Row Count: ' . $count . '<br><br>';
            while($row = $query->fetch_array()){
                echo 'Row ' . $loop . ' => ' . 
                'Min: ' . $row['MIN(price)'] . '<br><br>';
                
                $loop++;
            }
            
            echo '</div>';
        } else{
            echo "No Rows Found in Table tbl_cars!";
        }
    }
}
//پایان اتصال
$conn->close();
?>

خروجی نمونه دستورات بالا به صورت زیر خواهد بود:

Row Count: 1
Row 1 => Min: 1000000

در صورتی که بخواهیم به همراه ستون کمترین قیمت (price) ستون name متناظر را هم استخراج کنیم باید از GROUP BY و ORDER BY به شکل پرس و جوهای نمونه زیر در کنار تابع MIN استفاده کنیم:

SELECT name, MIN(price) FROM tbl_cars WHERE id != 0 GROUP BY price ORDER BY price ASC

SELECT name, MIN(price) FROM tbl_cars WHERE id != 0 GROUP BY price  ORDER BY price ASC LIMIT 1

در صورت استفاده از LIMIT، نتایج محدود به تعداد تعیین شده بر اساس کمترین مقدار قیمت خواهد بود.

تابع تجمیعی MAX، انتخاب بیشترین مقدار

با تابع MAX می توانیم بیشترین مقدار را از بین نتایج پرس و جو استخراج کنیم، در استفاده از تابع تجمیعی MAX مشابه MIN باید ستون مورد نظر را تعیین کنیم، نوع ستون باید عددی باشد به فرض:

SELECT MAX(price) FROM tbl_cars WHERE id != 0

برای تست و بررسی تابع MAX کافی است پرس و جوی بالا را در قسمت SQL برنامه phpMyAdmin اجرا یا کد زیر را در یک فایل PHP درج و پس از اعمال تنظیمات اتصال در متغیر conn از طریق مرورگر فراخوانی کنیم.
برنامه نویسی رویه ای (Procedural):

<?php
//اتصال به دیتابیس
$conn = mysqli_connect("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . mysqli_connect_errno() . ' - ' . mysqli_connect_error();
    exit;
} else{    
    //سازگاری با حروف فارسی
    $sql = "SET NAMES 'utf8'";
    $query = mysqli_query($conn, $sql);
    
    //اانتخاب اطلاعات از جدول و ستون ها
    $sql = "SELECT MAX(price) FROM tbl_cars WHERE id != 0";
    $query = mysqli_query($conn, $sql);
    
    if(!$query){
        echo "Selecting From Table tbl_cars: Error! " . mysqli_error($conn) . '<br>';
    } else{
        //تعداد ردیف های انتخاب شده
        $count = mysqli_num_rows($query);
        $loop = 1;
        
        //پردازش و خروجی ردیف های اطلاعات
        if($count > 0){
            echo '<div style="direction: ltr; border: 1px solid #999;">';
            
            echo 'Row Count: ' . $count . '<br><br>';
            while($row = mysqli_fetch_array($query)){
                echo 'Row ' . $loop . ' => ' . 
                'Max: ' . $row['MAX(price)'] . '<br><br>';
                
                $loop++;
            }
            
            echo '</div>';
        } else{
            echo "No Rows Found in Table tbl_cars!";
        }
    }
}
//پایان اتصال
mysqli_close($conn);
?>

برنامه نویسی شی گرا (Object-oriented):

<?php
//اتصال به دیتابیس
$conn = new mysqli("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . $conn->errno . ' - ' . $conn->error;
    exit;
} else {
    //سازگاری با حروف فارسی
    $sql = "SET NAMES 'utf8'";
    $conn->query($sql);
    
    //اانتخاب اطلاعات از جدول و ستون ها
    $sql = "SELECT MAX(price) FROM tbl_cars WHERE id != 0";
    $query = $conn->query($sql);
    
    //دریافت اطلاعات از جدول
    if(!$query) {
        echo "Selecting From Table tbl_cars: Error! " . $conn->error . '<br>';
    } else {
        //تعداد ردیف های انتخاب شده
        $count = $query->num_rows;
        $loop = 1;
        
        //پردازش و خروجی ردیف های اطلاعات
        if($count > 0){
            echo '<div style="direction: ltr; border: 1px solid #999;">';
            
            echo 'Row Count: ' . $count . '<br><br>';
            while($row = $query->fetch_array()){
                echo 'Row ' . $loop . ' => ' . 
                'Max: ' . $row['MAX(price)'] . '<br><br>';
                
                $loop++;
            }
            
            echo '</div>';
        } else{
            echo "No Rows Found in Table tbl_cars!";
        }
    }
}
//پایان اتصال
$conn->close();
?>

خروجی نمونه دستورات بالا به صورت زیر خواهد بود:

Row Count: 1
Row 1 => Max: 1500000

در صورتی که بخواهیم به همراه ستون بیشترین قیمت (price) ستون name متناظر را هم استخراج کنیم باید از GROUP BY و ORDER BY به شکل پرس و جوهای نمونه زیر در کنار تابع MAX استفاده کنیم:

SELECT name, MAX(price) FROM tbl_cars WHERE id != 0 GROUP BY price ORDER BY price DESC

SELECT name, MAX(price) FROM tbl_cars WHERE id != 0 GROUP BY price ORDER BY price DESC LIMIT 1

در صورت استفاده از LIMIT، نتایج محدود به تعداد تعیین شده بر اساس بیشترین مقدار قیمت خواهد بود.

تابع تجمیعی AVG، میانگین مقادیر ردیف ها

در کنار توابع MIN و MAX که برای استخراج کمترین و بیشترین مقدار از نتایج پرس و جو به کار می روند تابع دیگری تحت عنوان AVG (برگرفته از عبارت Average) برای محاسبه میانگین مقادیر ردیف های پرس و جو کاربرد دارد، در استفاده از تابع تجمیعی AVG باید ستون مورد نظر را تعیین کنیم، نوع ستون باید عددی باشد به فرض:

SELECT AVG(price) FROM tbl_cars WHERE id != 0

برای تست و بررسی تابع AVG کافی است پرس و جوی بالا را در قسمت SQL برنامه phpMyAdmin اجرا یا کد زیر را در یک فایل PHP درج و پس از اعمال تنظیمات اتصال در متغیر conn از طریق مرورگر فراخوانی کنیم.
برنامه نویسی رویه ای (Procedural):

<?php
//اتصال به دیتابیس
$conn = mysqli_connect("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . mysqli_connect_errno() . ' - ' . mysqli_connect_error();
    exit;
} else{    
    //سازگاری با حروف فارسی
    $sql = "SET NAMES 'utf8'";
    $query = mysqli_query($conn, $sql);
    
    //اانتخاب اطلاعات از جدول و ستون ها
    $sql = "SELECT AVG(price) FROM tbl_cars WHERE id != 0";
    $query = mysqli_query($conn, $sql);
    
    if(!$query){
        echo "Selecting From Table tbl_cars: Error! " . mysqli_error($conn) . '<br>';
    } else{
        //تعداد ردیف های انتخاب شده
        $count = mysqli_num_rows($query);
        $loop = 1;
        
        //پردازش و خروجی ردیف های اطلاعات
        if($count > 0){
            echo '<div style="direction: ltr; border: 1px solid #999;">';
            
            echo 'Row Count: ' . $count . '<br><br>';
            while($row = mysqli_fetch_array($query)){
                echo 'Row ' . $loop . ' => ' .
                'Average: ' . $row['AVG(price)'] . '<br><br>';
                
                $loop++;
            }
            
            echo '</div>';
        } else{
            echo "No Rows Found in Table tbl_cars!";
        }
    }
}
//پایان اتصال
mysqli_close($conn);
?>

برنامه نویسی شی گرا (Object-oriented):

<?php
//اتصال به دیتابیس
$conn = new mysqli("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . $conn->errno . ' - ' . $conn->error;
    exit;
} else {
    //سازگاری با حروف فارسی
    $sql = "SET NAMES 'utf8'";
    $conn->query($sql);
    
    //اانتخاب اطلاعات از جدول و ستون ها
    $sql = "SELECT AVG(price) FROM tbl_cars WHERE id != 0";
    $query = $conn->query($sql);
    
    //دریافت اطلاعات از جدول
    if(!$query) {
        echo "Selecting From Table tbl_cars: Error! " . $conn->error . '<br>';
    } else {
        //تعداد ردیف های انتخاب شده
        $count = $query->num_rows;
        $loop = 1;
        
        //پردازش و خروجی ردیف های اطلاعات
        if($count > 0){
            echo '<div style="direction: ltr; border: 1px solid #999;">';
            
            echo 'Row Count: ' . $count . '<br><br>';
            while($row = $query->fetch_array()){
                echo 'Row ' . $loop . ' => ' . 
                'Average: ' . $row['AVG(price)'] . '<br><br>';
                
                $loop++;
            }
            
            echo '</div>';
        } else{
            echo "No Rows Found in Table tbl_cars!";
        }
    }
}
//پایان اتصال
$conn->close();
?>

خروجی نمونه دستورات بالا به صورت زیر خواهد بود:

Row Count: 1
Row 1 => Average: 1250000.0000

مشابه آنچه در مورد تابع COUNT و شمارش ردیف ها گفتیم در اینجا هم اگر بخواهیم به همراه میانگین عددی مقادیر، نام سایر ردیف ها را در نتیجه پرس و جو داشته باشیم باید از نمونه دستور SQL مشابه زیر استفاده کنیم:

SELECT name, (SELECT AVG(price) FROM tbl_cars WHERE name != '') AS average_price FROM tbl_cars WHERE name != ''

در قسمت دریافت و چاپ نتایج در حلقه while هم به جای حالت بالا از کلید average_price برای آرایه row استفاده می کنیم:

while($row = mysqli_fetch_array($query)){
    echo 'Row ' . $loop . ' => ' .
    'Name: ' . $row['name'] . ' - ' . 
    'Averag: ' . $row['average_price'] . '<br><br>';
    
    $loop++;
}

که خروج به شکل زیر خواهد بود:

Row Count: 6
Row 1 => Name: Dena - Averag: 1250000.0000
Row 2 => Name: Mazda - Averag: 1250000.0000
Row 3 => Name: Nissan - Averag: 1250000.0000
Row 4 => Name: Peugeot - Averag: 1250000.0000
Row 5 => Name: Pride - Averag: 1250000.0000
Row 6 => Name: Tiba - Averag: 1250000.0000

به این صورت علاوه بر محاسبه میانگین عددی مقدار ردیف ها در قسمت AVG، مقادیر سایر ردیف ها نیز قابل دسترسی است.

تابع تجمیعی SUM، مجموع مقادیر ردیف ها

برای محاسبه مجموع مقادیر ردیف ها در پرس و جوهای MySQL تابع تجمیعی SUM کاربرد دارد، در استفاده از تابع تجمیعی SUM باید ستون مورد نظر را تعیین کنیم، نوع ستون باید عددی باشد به فرض:

SELECT SUM(price) FROM tbl_cars WHERE id != 0

برای تست و بررسی تابع SUM کافی است پرس و جوی بالا را در قسمت SQL برنامه phpMyAdmin اجرا یا کد زیر را در یک فایل PHP درج و پس از اعمال تنظیمات اتصال در متغیر conn از طریق مرورگر فراخوانی کنیم.
برنامه نویسی رویه ای (Procedural):

<?php
//اتصال به دیتابیس
$conn = mysqli_connect("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . mysqli_connect_errno() . ' - ' . mysqli_connect_error();
    exit;
} else{    
    //سازگاری با حروف فارسی
    $sql = "SET NAMES 'utf8'";
    $query = mysqli_query($conn, $sql);
    
    //اانتخاب اطلاعات از جدول و ستون ها
    $sql = "SELECT SUM(price) FROM tbl_cars WHERE id != 0";
    $query = mysqli_query($conn, $sql);
    
    if(!$query){
        echo "Selecting From Table tbl_cars: Error! " . mysqli_error($conn) . '<br>';
    } else{
        //تعداد ردیف های انتخاب شده
        $count = mysqli_num_rows($query);
        $loop = 1;
        
        //پردازش و خروجی ردیف های اطلاعات
        if($count > 0){
            echo '<div style="direction: ltr; border: 1px solid #999;">';
            
            echo 'Row Count: ' . $count . '<br><br>';
            while($row = mysqli_fetch_array($query)){
                echo 'Row ' . $loop . ' => ' .
                'Sum: ' . $row['SUM(price)'] . '<br><br>';
                
                $loop++;
            }
            
            echo '</div>';
        } else{
            echo "No Rows Found in Table tbl_cars!";
        }
    }
}
//پایان اتصال
mysqli_close($conn);
?>

برنامه نویسی شی گرا (Object-oriented):

<?php
//اتصال به دیتابیس
$conn = new mysqli("localhost", "username", "password", "dbname");
if(!$conn){
    echo "PHP & MySQL Connection: Error! " . $conn->errno . ' - ' . $conn->error;
    exit;
} else {
    //سازگاری با حروف فارسی
    $sql = "SET NAMES 'utf8'";
    $conn->query($sql);
    
    //اانتخاب اطلاعات از جدول و ستون ها
    $sql = "SELECT SUM(price) FROM tbl_cars WHERE id != 0";
    $query = $conn->query($sql);
    
    //دریافت اطلاعات از جدول
    if(!$query) {
        echo "Selecting From Table tbl_cars: Error! " . $conn->error . '<br>';
    } else {
        //تعداد ردیف های انتخاب شده
        $count = $query->num_rows;
        $loop = 1;
        
        //پردازش و خروجی ردیف های اطلاعات
        if($count > 0){
            echo '<div style="direction: ltr; border: 1px solid #999;">';
            
            echo 'Row Count: ' . $count . '<br><br>';
            while($row = $query->fetch_array()){
                echo 'Row ' . $loop . ' => ' . 
                'Sum: ' . $row['SUM(price)'] . '<br><br>';
                
                $loop++;
            }
            
            echo '</div>';
        } else{
            echo "No Rows Found in Table tbl_cars!";
        }
    }
}
//پایان اتصال
$conn->close();
?>

خروجی نمونه دستورات بالا به صورت زیر خواهد بود:

Row Count: 1
Row 1 => Sum: 7500000

مشابه آنچه در مورد تابع COUNT و AVG گفتیم در اینجا هم اگر بخواهیم به همراه مجموع مقادیر نام سایر ردیف ها را در نتیجه پرس و جو داشته باشیم باید از نمونه دستور SQL مشابه زیر استفاده کنیم:

SELECT name, (SELECT SUM(price) FROM tbl_cars WHERE name != '') AS sum_price FROM tbl_cars WHERE name != ''

در قسمت دریافت و چاپ نتایج در حلقه while هم به جای حالت بالا از کلید sum_price برای آرایه row استفاده می کنیم:

while($row = mysqli_fetch_array($query)){
    echo 'Row ' . $loop . ' => ' .
    'Name: ' . $row['name'] . ' - ' . 
    'Sum: ' . $row['sum_price'] . '<br><br>';
    
    $loop++;
}

که خروج به شکل زیر خواهد بود:

Row Count: 6
Row 1 => Name: Dena - Sum: 7500000
Row 2 => Name: Mazda - Sum: 7500000
Row 3 => Name: Nissan - Sum: 7500000
Row 4 => Name: Peugeot - Sum: 7500000
Row 5 => Name: Pride - Sum: 7500000
Row 6 => Name: Tiba - Sum: 7500000

به این صورت علاوه بر محاسبه مجموع مقدار ردیف ها در قسمت SUM، مقادیر سایر ردیف ها نیز قابل دسترسی است.

ترکیب توابع تجمیعی با HAVING و DISTINCT

در آموزش گذشته در خصوص محدودسازی، گروه بندی و مرتب سازی نتایج در MySQL صحبت کردیم و اشاره شد که دستورات HAVING و DISTINCT را می توانیم با توابع تجمیعی ترکیب و پرس و جوهای توسعه یافته تری بنویسیم، از این رو در انتهای این مطلب چند نمونه پرس و جو از نحوه ترکیب توابع تجمیعی با HAVING و DISTINCT را جهت آشنایی بیشتر با هم بررسی می کنیم، برای تست پرس و جوها کافی است در قسمت sql برنامه phpMyAdmin دستورات زیر را مورد به مورد اجرا کنیم:
- استفاده از تابع COUNT و HAVING
نمونه پرس و جو:

SELECT color, (SELECT COUNT(id) FROM tbl_cars WHERE name != '') AS count_id FROM tbl_cars WHERE model > 1350 GROUP BY name HAVING color = 'white'

خروجی:

color     count_id     
white        6
white        6

- استفاده از تابع COUNT و DISTINCT
نمونه پرس و جو:

SELECT COUNT(DISTINCT color) FROM tbl_cars WHERE model > 1350

خروجی:

4

- استفاده از تابع MIN و HAVING
نمونه پرس و جو:

SELECT name, MIN(price) FROM tbl_cars WHERE id != 0 GROUP BY price HAVING name != 'Tiba' ORDER BY price ASC

خروجی:

 name    MIN(price)     
Pride     1000000
Nissan    1200000
Mazda     1300000
Dena      1400000
Peugeot   1500000

- استفاده از تابع MIN و DISTINCT
نمونه پرس و جو:

SELECT DISTINCT color, MIN(price) FROM tbl_cars WHERE id != 0 GROUP BY price ORDER BY price ASC

خروجی:

 color   MIN(price)     
white     1000000
white     1100000
blue      1200000
silver    1300000
black     1400000
black     1500000

زمانی که دو یا چند ستون بعد از دستور DISTINCT تعیین می شود باید مقادیر تکراری در هر دو ستون و دو ردیف وجود داشته باشد تا یکی از آنها حذف شود، در نمونه اطلاعات جدول tbl_cars تنها ستون رنگ (color) دارای مقادیر تکراری است و قیمت (price) متفاوت است لذا حذف انجام نمی شود.
- استفاده از تابع MAX و HAVING
نمونه پرس و جو:

SELECT name, MAX(price) FROM tbl_cars WHERE id != 0 GROUP BY price HAVING name != 'Tiba' ORDER BY price DESC

خروجی:

 name     MAX(price)     
Peugeot    1500000
Dena       1400000
Mazda      1300000
Nissan     1200000
Pride      1000000

- استفاده از تابع MAX و DISTINCT
نمونه پرس و جو:

SELECT DISTINCT color, MAX(price) FROM tbl_cars WHERE id != 0 GROUP BY price ORDER BY price DESC

خروجی:

 color     MAX(price)     
black       1500000
black       1400000
silver      1300000
blue        1200000
white       1100000
white       1000000

- استفاده از تابع AVG و HAVING
نمونه پرس و جو:

SELECT color, (SELECT AVG(price) FROM tbl_cars WHERE name != '') AS average_price FROM tbl_cars WHERE name != '' GROUP BY name HAVING color = 'black'

خروجی:

 color     average_price     
black      1250000.0000
black      1250000.0000

- استفاده از تابع AVG و DISTINCT
نمونه پرس و جو:

SELECT AVG(DISTINCT price) FROM tbl_cars WHERE model > 1350

خروجی:

AVG(DISTINCT price)     
1250000.0000

در این مثال هم چون هیچ قیمت (price) تکراری نداریم در محاسبه میانگین هیچ کدام از ردیف ها حذف نمی شود.
- استفاده از تابع SUM و HAVING
نمونه پرس و جو:

SELECT color, (SELECT SUM(price) FROM tbl_cars WHERE name != '') AS sum_price FROM tbl_cars WHERE model > 1350 GROUP BY name HAVING color != 'blue'

خروجی:

 color     sum_price     
black       7500000
silver      7500000
black       7500000
white       7500000
white       7500000

- استفاده از تابع SUM و DISTINCT
نمونه پرس و جو:

SELECT SUM(DISTINCT price) FROM tbl_cars WHERE model > 1350

خروجی:

SUM(DISTINCT price)     
7500000

برای تست و بررسی بیشتر می توانیم قیمت ها را در ستون price به صورت تکراری تعیین و مجددا پرس و جو را اجرا کنیم.