{"id":2261,"date":"2020-03-02T08:22:50","date_gmt":"2020-03-02T08:22:50","guid":{"rendered":"https:\/\/studysection.com\/blog\/?p=2261"},"modified":"2020-03-02T09:04:34","modified_gmt":"2020-03-02T09:04:34","slug":"aggregate-functions-in-sql","status":"publish","type":"post","link":"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/","title":{"rendered":"The Aggregate Functions in SQL"},"content":{"rendered":"

What is the aggregate function in MySQL?<\/h2>\n

Aggregate functions are a bunch of methods that work on a set of values. They can do calculations for us. After that, they return one final value.<\/p>\n

The following five aggregate functions are defined by the ISO standard:<\/p>\n

    \n
  1. COUNT function<\/li>\n
  2. MIN function<\/li>\n
  3. MAX function<\/li>\n
  4. SUM function<\/li>\n
  5. AVG function<\/li>\n<\/ol>\n

    Example Table<\/h3>\n

    create table employee_details (Id int (10), Emp_name varchar (40), dept varchar (40), Emp_salary int (10));<\/strong>
    \n
    \nINSERT INTO employee_details (
    \nId,
    \nEmp_name,
    \nDept, Emp_salary)
    \nVALUES
    \n(1, 'Mahesh', 'HR', 50000),
    \n(2, 'Rajesh', 'HR', 60000),
    \n(3, 'Anil', 'Finance', 50000),
    \n(4, 'Shikha', 'Admin', Null),
    \n(5, 'Ritul', 'IT', 65000),
    \n(6, 'Kamal', 'IT', 80000);
    \n<\/code><\/p>\n

    Max:<\/h3>\n

    The aggregate function SQL MAX() is used to get the highest value or maximum value of a particular column or expression. This function is helpful to determine the largest of all selected values of a column.<\/strong><\/p>\n

    SELECT MAX(Emp_SALARY) FROM employee_details;<\/code><\/p>\n

    Min:<\/h3>\n

    The smallest value of the selected column is returned by the MIN() function.<\/strong><\/p>\n

    SELECT MIN(Emp_SALARY) FROM employee_details;<\/code><\/p>\n

    Count:<\/h3>\n

    The COUNT() function returns the number of rows that matches specified criteria.<\/p>\n

    To get the total number of rows in a table if any Null values exist in the Table:<\/strong>
    \nSelect Count(*) from employee_details;<\/code>
    \nTo get the total number of salary in a table if any Null Values exist in the Table:<\/strong><\/p>\n

    Select Count(emp_salary) from employee_details;<\/code><\/p>\n

    To get the unique values in a Table (It will not count the duplicate value, It will show only 1 in case of multiple duplicate values):<\/strong><\/p>\n

    select count( DISTINCT (emp_salary)) from employee_details;<\/code><\/p>\n

    Sum:<\/h3>\n

    The total sum of a numeric column is returned by the SUM() function.<\/p>\n

    To get a sum of the salary:<\/strong><\/p>\n

    select sum(Emp_salary) from employee_details;<\/code><\/p>\n

    To get a sum of the salary except for the duplicate values<\/strong><\/p>\n

    select sum(DISTINCT (emp_salary)) from employee_details;<\/code><\/p>\n

    Average:<\/h3>\n

    The average value of a numeric column is returned by the AVG() function.<\/strong><\/p>\n

    select avg(emp_salary) from employee_details;<\/p>\n

    Select avg(distinct(emp_salary)) from employee_details;<\/code><\/p>\n

    The SQL GROUP BY Statement:<\/h3>\n

    The GROUP BY statement is a group of rows that have similar values into summary rows, like “find the number of consumers in each country”.
    \nThe GROUP BY statement is usually used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
    \nTo get the employees to detail in all the Dept from the Table:<\/strong>
    \nselect dept from employee_details group by dept;<\/code>
    \nTo get the total number of employees in every dept:<\/strong>
    \nselect dept, count(*) from employee_details group by dept;<\/code><\/p>\n

    The SQL Having Clause:<\/h3>\n

    Having Clause is commonly used with the GROUP BY clause to filter the groups based on a specified list of conditions.<\/p>\n

    To get the employee detail less than 2 in all the Dept from the Table select dept from employee_details group by dept having count(*):<\/strong><\/p>\n

    select dept from employee_details group by dept having count(*)<\/code><\/p>\n

    Individuals that have a good command over English have more chances of getting hired. An English certification<\/a> can help in such situations. English certification exam from StudySection includes basic as well as advanced level English language concepts.<\/em><\/small><\/p>\n","protected":false},"excerpt":{"rendered":"

    What is the aggregate function in MySQL? Aggregate functions are a bunch of methods that work on a set of<\/p>\n","protected":false},"author":1,"featured_media":2262,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[382,306],"acf":[],"yoast_head":"\nStudySection Blog - The Aggregate Functions in SQL<\/title>\n<meta name=\"description\" content=\"Aggregate functions are a bunch of methods that work on a set of values. They can do calculations for us. After that, they return one final value.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"StudySection Blog - The Aggregate Functions in SQL\" \/>\n<meta property=\"og:description\" content=\"Aggregate functions are a bunch of methods that work on a set of values. They can do calculations for us. After that, they return one final value.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog Posts on famous people, innovations and educational topics\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/studysection\" \/>\n<meta property=\"article:published_time\" content=\"2020-03-02T08:22:50+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-03-02T09:04:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2020\/03\/aggregate-sql.png\" \/>\n\t<meta property=\"og:image:width\" content=\"300\" \/>\n\t<meta property=\"og:image:height\" content=\"200\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"admin-studysection-blog\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@studysection\" \/>\n<meta name=\"twitter:site\" content=\"@studysection\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin-studysection-blog\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/\"},\"author\":{\"name\":\"admin-studysection-blog\",\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/person\/db367e2c29a12d1808fb1979edb3d402\"},\"headline\":\"The Aggregate Functions in SQL\",\"datePublished\":\"2020-03-02T08:22:50+00:00\",\"dateModified\":\"2020-03-02T09:04:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/\"},\"wordCount\":411,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/studysection.com\/blog\/#organization\"},\"keywords\":[\"Functions\",\"SQL\"],\"articleSection\":[\"Learn and Grow\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/\",\"url\":\"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/\",\"name\":\"StudySection Blog - The Aggregate Functions in SQL\",\"isPartOf\":{\"@id\":\"https:\/\/studysection.com\/blog\/#website\"},\"datePublished\":\"2020-03-02T08:22:50+00:00\",\"dateModified\":\"2020-03-02T09:04:34+00:00\",\"description\":\"Aggregate functions are a bunch of methods that work on a set of values. They can do calculations for us. After that, they return one final value.\",\"breadcrumb\":{\"@id\":\"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/studysection.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Aggregate Functions in SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/studysection.com\/blog\/#website\",\"url\":\"https:\/\/studysection.com\/blog\/\",\"name\":\"Blog Posts on famous people, innovations and educational topics\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/studysection.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/studysection.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/studysection.com\/blog\/#organization\",\"name\":\"StudySection\",\"url\":\"https:\/\/studysection.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2021\/10\/studySection-logo.png\",\"contentUrl\":\"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2021\/10\/studySection-logo.png\",\"width\":920,\"height\":440,\"caption\":\"StudySection\"},\"image\":{\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/studysection\",\"https:\/\/twitter.com\/studysection\",\"https:\/\/www.instagram.com\/study.section\/\",\"https:\/\/www.linkedin.com\/company\/studysection\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/person\/db367e2c29a12d1808fb1979edb3d402\",\"name\":\"admin-studysection-blog\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/054ac87a6874df1932004239cd8eab36?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/054ac87a6874df1932004239cd8eab36?s=96&d=mm&r=g\",\"caption\":\"admin-studysection-blog\"},\"url\":\"https:\/\/studysection.com\/blog\/author\/admin-studysection-blog\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"StudySection Blog - The Aggregate Functions in SQL","description":"Aggregate functions are a bunch of methods that work on a set of values. They can do calculations for us. After that, they return one final value.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/","og_locale":"en_US","og_type":"article","og_title":"StudySection Blog - The Aggregate Functions in SQL","og_description":"Aggregate functions are a bunch of methods that work on a set of values. They can do calculations for us. After that, they return one final value.","og_url":"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/","og_site_name":"Blog Posts on famous people, innovations and educational topics","article_publisher":"https:\/\/www.facebook.com\/studysection","article_published_time":"2020-03-02T08:22:50+00:00","article_modified_time":"2020-03-02T09:04:34+00:00","og_image":[{"width":300,"height":200,"url":"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2020\/03\/aggregate-sql.png","type":"image\/png"}],"author":"admin-studysection-blog","twitter_card":"summary_large_image","twitter_creator":"@studysection","twitter_site":"@studysection","twitter_misc":{"Written by":"admin-studysection-blog","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/#article","isPartOf":{"@id":"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/"},"author":{"name":"admin-studysection-blog","@id":"https:\/\/studysection.com\/blog\/#\/schema\/person\/db367e2c29a12d1808fb1979edb3d402"},"headline":"The Aggregate Functions in SQL","datePublished":"2020-03-02T08:22:50+00:00","dateModified":"2020-03-02T09:04:34+00:00","mainEntityOfPage":{"@id":"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/"},"wordCount":411,"commentCount":0,"publisher":{"@id":"https:\/\/studysection.com\/blog\/#organization"},"keywords":["Functions","SQL"],"articleSection":["Learn and Grow"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/","url":"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/","name":"StudySection Blog - The Aggregate Functions in SQL","isPartOf":{"@id":"https:\/\/studysection.com\/blog\/#website"},"datePublished":"2020-03-02T08:22:50+00:00","dateModified":"2020-03-02T09:04:34+00:00","description":"Aggregate functions are a bunch of methods that work on a set of values. They can do calculations for us. After that, they return one final value.","breadcrumb":{"@id":"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/studysection.com\/blog\/aggregate-functions-in-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/studysection.com\/blog\/"},{"@type":"ListItem","position":2,"name":"The Aggregate Functions in SQL"}]},{"@type":"WebSite","@id":"https:\/\/studysection.com\/blog\/#website","url":"https:\/\/studysection.com\/blog\/","name":"Blog Posts on famous people, innovations and educational topics","description":"","publisher":{"@id":"https:\/\/studysection.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/studysection.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/studysection.com\/blog\/#organization","name":"StudySection","url":"https:\/\/studysection.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/studysection.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2021\/10\/studySection-logo.png","contentUrl":"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2021\/10\/studySection-logo.png","width":920,"height":440,"caption":"StudySection"},"image":{"@id":"https:\/\/studysection.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/studysection","https:\/\/twitter.com\/studysection","https:\/\/www.instagram.com\/study.section\/","https:\/\/www.linkedin.com\/company\/studysection"]},{"@type":"Person","@id":"https:\/\/studysection.com\/blog\/#\/schema\/person\/db367e2c29a12d1808fb1979edb3d402","name":"admin-studysection-blog","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/studysection.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/054ac87a6874df1932004239cd8eab36?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/054ac87a6874df1932004239cd8eab36?s=96&d=mm&r=g","caption":"admin-studysection-blog"},"url":"https:\/\/studysection.com\/blog\/author\/admin-studysection-blog\/"}]}},"views":359,"_links":{"self":[{"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/posts\/2261"}],"collection":[{"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/comments?post=2261"}],"version-history":[{"count":5,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/posts\/2261\/revisions"}],"predecessor-version":[{"id":2267,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/posts\/2261\/revisions\/2267"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/media\/2262"}],"wp:attachment":[{"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/media?parent=2261"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/categories?post=2261"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/tags?post=2261"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}