What is SQL GROUP BY Clause?

 When working in SQL, results can be grouped according to a specific field or fields.




SQL: GROUP BY Clause

When working in SQL, results can be grouped according to a specific field or fields.

- GROUP BY must be placed immediately after the WHERE conditions, if any, and just before the ORDER BY clause.

- GROUP BY is one of the most powerful and useful tools in SQL

Syntax

The syntax for the GROUP BY clause in SQL is:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
[ORDER BY expression [ ASC | DESC ]];

Note:

  • In most cases, when you need an aggregate function, you must add a GROUP BY clause in your query, too.
  • Always include the field you have grouped your results by in the SELECT statement!
Example - Using GROUP BY
employee_number last_name first_name salary dept_id
1001 Smith John 62000 500
1002 Anderson Jane 57500 500
1003 Everest Brad 71000 501
1004 Horvath Jack 42000 501

Enter the following SQL statement:

SELECT dept_id, SUM(salary) AS total_salaries
FROM employees
GROUP BY dept_id;

There will be 2 records selected. These are the results that you should see:
dept_id total_salaries
500 119500
501 113000

In this example, we've used the SUM function to add up all of the salaries for each dept_id and we've aliased the results of the SUM function as total_salaries. Because the dept_id is not encapsulated in the SUM function, it must be listed in the GROUP BY clause.

Post a Comment

0 Comments

close