When there are so many records with same value (same value of a column in each row) and there is need to have only one record of all rows for that particular column, indeed, accumulated values of rest of columns in the rows. In other words, if we need summation or gathered values of different rows in one result so we would utilize the condition ‘group by’ in MySql query.

select * from table_name group by column name

Suppose we have database of different invoices of sale of inventory items. One row show one item and when there are more than one row in an invoice, so invoice number would be same for all items so in all rows of database, column of invoice number would be same. In this scenario, we would extract results for an invoice taking invoice numberĀ  group by. The sale amount of each one item would be totaled at glance as invoice amount.

In MySql, table of sales_invoices;

 

invoice_no invoice_date item_name amount
000123 2018-09-03 Item abc 100
000123 2018-09-03 Item xyx 400
000123 2018-09-03 Item zzz 200

 

 

Now we use PHP to extract value of the above invoice.

$invoice = $db_connection->prepare("select *, sum(amount) from sales_invoices group by invoice_no");
$invoice->execute();
while($inv_result = $invoice->fetch(PDO::FETCH_ASSOC)){
echo $inv_result['invoice_no'].' - - - '.$inv_result['sum(amount0'];
}

The result would be: 000123 – – – 700