Scribd Logo
Upload

Welcome to Scribd!

  • 55 views

    Aggregate Functions

    Uploaded by

    Zhel Geron Mercado
    Aggregate functions are used to calculate results using field values from multiple records. Common aggregate functions in MySQL include COUNT, SUM, AVG, MAX, and MIN. These functions can be used with the GROUP BY clause of SELECT statements to generate summary data from tables.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd

    Aggregate Functions

    Uploaded by

    Zhel Geron Mercado
    55 views3 pages
    Aggregate functions are used to calculate results using field values from multiple records. Common aggregate functions in MySQL include COUNT, SUM, AVG, MAX, and MIN. These functions can be used with the GROUP BY clause of SELECT statements to generate summary data from tables.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Aggregate functions are used to calculate results using field values from multiple records. Common aggregate functions in MySQL include COUNT, SUM, AVG, MAX, and MIN. These functions can be used with the GROUP BY clause of SELECT statements to generate summary data from tables.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    55 views3 pages

    Aggregate Functions

    Uploaded by

    Zhel Geron Mercado
    Aggregate functions are used to calculate results using field values from multiple records. Common aggregate functions in MySQL include COUNT, SUM, AVG, MAX, and MIN. These functions can be used with the GROUP BY clause of SELECT statements to generate summary data from tables.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 3

    MySQL Aggregate Functions

    Aggregate functions are used to calculate results using field values from multiple records. Aggregate functions ignore null value when performing calculation except COUNT function. Aggregate functions are often used with GROUP BY clause of SELECT statement.

    Common Aggregate Functions Aggregate Function COUNT() SUM() AVG() MAX() MIN() Description Returns the number of rows containing non-NULL values in the specified field. Returns the SUM of the non-NULL values in the specified field. Returns the average of the non-NULL values in the specified field. Returns the maximum of the non-NULL values in the specified field. Returns the minimum of the non-NULL values in the specified field.

    SUM FUNCTION Table Name: OrderDetails Table Name: Product

    SELECT ProductCode, SUM(QuantityOrdered * PriceEach) TOTAL from OrderDetails GROUP BY ProductCode;

    SELECT p.ProductCode, p.ProductName, SUM (PriceEach * QuantityOrdered) TOTAL from OrderDetails O INNER JOIN Product P on O.ProductCode = p.ProductCode GROUP BY ProductCode ORDER BY TOTAL;

    Page 1

    MySQL Aggregate Functions


    AVG FUNCTION AVG is used to calculate average value of an expression. It ignores NULL values. We can use AVG function to calculate the average price of all products buy executing the following query.
    AVG(expression)

    SELECT AVG (PriceEach) average_price from OrderDetails;

    MAX and MIN Function MAX function returns the maximum and MIN function returns the minimum value of the set of values in expression. MAX(expression) MIN(expression)

    SELECT MAX (PriceEach) Highest_Price, MIN (PriceEach) Lowest_Price from OrderDetails;

    COUNT Function COUNT function returns the count of the items in expression. The syntax is quite simple as follows: SELECT COUNT(*) AS total_founds FROM table_name SELECT COUNT(*) from Product;

    The query will return the total record of the table. We can use WHERE clause in SELECT statement to filter records in the table we want to count as follows: SELECT COUNT(*) AS total_founds FROM table_name WHERE conditions

    Page 2

    MySQL Aggregate Functions


    SELECT COUNT(*) as Total from Product;

    The query will only returns the number of records or rows which match the conditions in WHERE clause. Be noted that the COUNT(*) will return all rows in the database table even though a specified column value in a database table is NULL. In order to count records without NULL value in a specified column, we can use COUNT(column) instead. Here is the syntax: SELECT COUNT(column) FROM table_name SELECT COUNT (product.ProductName) as Order Name from product,OrderDetails WHERE product.ProductCode = OrderDetails.ProductCode AND OrderDetails.QuantityOrdered = 2;

    Use GROUP BY clauses to your SELECT statements to generate summary data Use HAVING and other clauses to your SELECT statements to filter the results returned by summarized data SELECT product.ProductCode, product.ProductName as "Car Name" ,OrderDetails.QuantityOrdered from product,OrderDetails WHERE product.ProductCode = OrderDetails.ProductCode GROUP BY ProductCode HAVING OrderDetails.QuantityOrdered = 2 ORDER BY product.ProductName;

    Page 3

    You might also like