w3resource

SQLite SELECT Query

Introduction

The SELECT statement is used to make a simple query from a database or a complicated query against some criteria. A SELECT statement does not make any changes to the database.

A query or SELECT statement is a command which gives instructions to a database to produce certain information(s) from the table in its memory. The SELECT command starts with the keyword SELECT followed by a space and a list of comma separated columns. A * character can be used to select all the columns of a table. The table name comes after the FROM keyword and a white-space.

Syntax:

SELECT [ALL | DISTINCT] result [FROM table-list]
[WHERE expr]
[GROUP BY expr-list]
[HAVING expr]
[compound-op select]*
[ORDER BY sort-expr-list]
[LIMIT integer [(OFFSET|,) integer]]

Parameters:

Clause Operation perform Input Value
WHERE Used to restrict Expression or condition
DISTINCT Used to restrict List of columns
FROM Joins List of tables
GROUP BY Used to restrict List of columns
ORDER BY   List of columns
HAVING Used to restrict Expression or condition
LIMIT Used to restrict Integer value
OFFSET Used to restrict Integer value

Here is the sample table employees.


Examples:

Retrieving all data:

If you want to retrieve all the rows from the locations table the following SQL can be used.

SELECT * FROM locations;

Relational Algebra Expression:

Relational Algebra Expression: SQLite Select Query Retrieving all data.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Select Query Retrieving all data.

Here is the result.

sqlite> SELECT * FROM locations;
location_id  street_address  postal_code  city        state_province  country_id
-----------  --------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola   989          Roma                        IT
1100         93091 Calle de  10934        Venice                      IT
1200         2017 Shinjuku-  1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-ch  6823         Hiroshima                   JP
1400         2014 Jabberwoc  26192        Southlake   Texas           US
1500         2011 Interiors  99236        South San   California      US
1600         2007 Zagora St  50090        South Brun  New Jersey      US
1700         2004 Charade R  98199        Seattle     Washington      US
1800         147 Spadina Av  M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood S  YSW 9T2      Whitehorse  Yukon           CA
2000         40-5-12 Laogia  190518       Beijing                     CN
2100         1298 Vileparle  490231       Bombay      Maharashtra     IN
2200         12-98 Victoria  2901         Sydney      New South Wale  AU
2300         198 Clementi N  540198       Singapore                   SG
2400         8204 Arthur St               London                      UK
2500         Magdalen Centr  OX9 9ZB      Oxford      Oxford          UK
2600         9702 Chester R  9629850293   Stretford   Manchester      UK
2700         Schwanthalerst  80925        Munich      Bavaria         DE
2800         Rua Frei Canec  01307-002    Sao Paulo   Sao Paulo       BR
2900         20 Rue des Cor  1730         Geneva      Geneve          CH
3000         Murtenstrasse   3095         Bern        BE              CH
3100         Pieter Breughe  3029SK       Utrecht     Utrecht         NL
3200         Mariano Escobe  11932        Mexico Cit  Distrito Feder  MX

Here in the above example the * have been used to indicate ALL the records from the locations table.

if FROM clause not used:

An expression can only be executed:

sqlite> SELECT 6+15;
6+15
----------
21

or

sqlite> SELECT 5*15;
5*15
----------
75

or

sqlite> SELECT 5+2-3*4/6;
5+2-3*4/6
----------
5

Select specific columns:

The SELECT statement can be used to retrieve specific columns. The column names follow the SELECT word.

If you want to retrieve the columns street_address and city from the location table the following SQL can be used.

SELECT street_address, city FROM locations;

Relational Algebra Expression:

Relational Algebra Expression: SQLite Select Query specific columns.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Select Query specific columns.

Here is the result.

sqlite> SELECT street_address, city FROM locations;
street_address                                      city
--------------------------------------------------  ----------
1297 Via Cola di Rie                                Roma
93091 Calle della Testa                             Venice
2017 Shinjuku-ku                                    Tokyo
9450 Kamiya-cho                                     Hiroshima
2014 Jabberwocky Rd                                 Southlake
2011 Interiors Blvd                                 South San
2007 Zagora St                                      South Brun
2004 Charade Rd                                     Seattle
147 Spadina Ave                                     Toronto
6092 Boxwood St                                     Whitehorse
40-5-12 Laogianggen                                 Beijing
1298 Vileparle (E)                                  Bombay
12-98 Victoria Street                               Sydney
198 Clementi North                                  Singapore
8204 Arthur St                                      London
Magdalen Centre, The Oxford Science Park            Oxford
9702 Chester Road                                   Stretford
Schwanthalerstr. 7031                               Munich
Rua Frei Caneca 1360                                Sao Paulo
20 Rue des Corps-Saints                             Geneva
Murtenstrasse 921                                   Bern
Pieter Breughelstraat 837                           Utrecht
Mariano Escobedo 9991                               Mexico Cit

Select columns using alias:

In the FROM list with a select statement, one or more table names can be included separated by a comma. The tables using followed by the FROM clause have an optional alias name that can be used to mention individual column names in the result.Where the table name in full can be used to qualify columns of a table, the aliases are not used.

sqlite> SELECT regions.region_name FROM regions;
region_name
-------------------------
REGION_NAME
Europe
Americas
Asia
Middle East and Africa

or

sqlite> SELECT z.region_name FROM regions z;
region_name
-------------------------
REGION_NAME
Europe
Americas
Asia
Middle East and Africa

Here in the above, the two SELECT statements are identical, the latter uses a table alias z for regions.

Renaming column names :

Sometimes we use the column name as short form against a keyword for that column. So the return value for that column will appear in short from, and sometimes we required to display it in full name. It can be done by using the AS clause followed the column name or any expression with the select statement.

If you want to retrieve the region_name column from the regions table with the customize column head "Name of the Region" the following SQL can be used. If the customize heading contain more than one words then it will be within a quote unquote unless it can be used independently.

Here is the example.

SELECT region_name AS "Name of the Region" FROM regions;

Here is the result.

Name of the Region
-------------------------
REGION_NAME
Europe
Americas
Asia
Middle East and Africa

Here in the following example, the customize heading contain more than one words and it used without quote unquote, and this statement occurs an error.

sqlite> SELECT region_name AS Name of the Region FROM regions;
Error: near "of": syntax error

Limit Output:

When we want to retrieve a limited amount of data from a large database we can use the LIMIT clause to limit the data amount returned by the statement.

If we want to retrieve the first ten records from the locations table, the following SQL can be used.

SELECT * FROM locations LIMIT 10;

Here is the result.

location_id                street_address        postal_code  city        state_province  country_id
-------------------------  --------------------  -----------  ----------  --------------  ----------
1000                       1297 Via Cola di Rie  989          Roma                        IT
1100                       93091 Calle della Te  10934        Venice                      IT
1200                       2017 Shinjuku-ku      1689         Tokyo       Tokyo Prefectu  JP
1300                       9450 Kamiya-cho       6823         Hiroshima                   JP
1400                       2014 Jabberwocky Rd   26192        Southlake   Texas           US
1500                       2011 Interiors Blvd   99236        South San   California      US
1600                       2007 Zagora St        50090        South Brun  New Jersey      US
1700                       2004 Charade Rd       98199        Seattle     Washington      US
1800                       147 Spadina Ave       M5V 2L7      Toronto     Ontario         CA
1900                       6092 Boxwood St       YSW 9T2      Whitehorse  Yukon           CA

If we want to retrieve five records skipping the first four from the locations table, the following SQL can be used.

SELECT * FROM locations LIMIT 4,5;

Here is the results.

sqlite> SELECT * FROM locations LIMIT 4,5;
location_id                street_address       postal_code  city        state_province  country_id
-------------------------  -------------------  -----------  ----------  --------------  ----------
1400                       2014 Jabberwocky Rd  26192        Southlake   Texas           US
1500                       2011 Interiors Blvd  99236        South San   California      US
1600                       2007 Zagora St       50090        South Brun  New Jersey      US
1700                       2004 Charade Rd      98199        Seattle     Washington      US
1800                       147 Spadina Ave      M5V 2L7      Toronto     Ontario         CA

Select using OFFSET clause:

The OFFSET clause after LIMIT specifies how many rows to skip at the beginning of the result set.

Here is the example.

SELECT * FROM locations LIMIT 5 OFFSET 4;

Here is the result is same as the previous result.

sqlite> SELECT * FROM locations LIMIT 5 OFFSET 4;
location_id                street_address       postal_code  city        state_province  country_id
-------------------------  -------------------  -----------  ----------  --------------  ----------
1400                       2014 Jabberwocky Rd  26192        Southlake   Texas           US
1500                       2011 Interiors Blvd  99236        South San   California      US
1600                       2007 Zagora St       50090        South Brun  New Jersey      US
1700                       2004 Charade Rd      98199        Seattle     Washington      US
1800                       147 Spadina Ave      M5V 2L7      Toronto     Ontario         CA

WHERE clause

WHERE is a powerful filter A WHERE clause can let you take exactly the piece of the data you want. It sets the conditions for the SELECT, and the query will return only those rows that match the conditions. It provides you with a great degree of control over the conditions to impose restrictions on the dataset returned by a SELECT with which to include (or exclude) rows in (or from) the result. It is used both to limit the number of rows returned and to indicate a relationship used to join two tables together.

If you only wanted to see cities from Canada, the following SQL can be used.

SELECT * FROM locations WHERE country_id='CA';

Relational Algebra Expression:

Relational Algebra Expression: SQLite Select Query WHERE clause.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Select Query WHERE clause.

Here is the result for the above statement.

sqlite> SELECT * FROM locations WHERE country_id='CA';
location_id  street_address   postal_code  city        state_province  country_id
-----------  ---------------  -----------  ----------  --------------  ----------
1800         147 Spadina Ave  M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood St  YSW 9T2      Whitehorse  Yukon           CA

Here some operators can be used with WHERE clause. Here are the operators below-

operator description
= Equal
<> Not equal*
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal

Here is a sample table agents.

AGENT_CODE  AGENT_NAME  WORKING_AREA  COMMISSION  PHONE_NO      COUNTRY
----------  ----------  ------------  ----------  ------------  ----------
A007        Ramasundar  Bangalore     0.15        077-25814763
A003        Alex        London        0.13        075-12458969
A008        Alford      New York      0.12        044-25874365
A011        Ravi Kumar  Bangalore     0.15        077-45625874
A010        Santakumar  Chennai       0.14        007-22388644
A012        Lucida      San Jose      0.12        044-52981425
A005        Anderson    Brisban       0.13        045-21447739
A001        Subbarao    Bangalore     0.14        077-12346674
A002        Mukesh      Mumbai        0.11        029-12358964
A006        McDen       London        0.15        078-22255588
A004        Ivan        Torento       0.15        008-22544166
A009        Benjamin    Hampshair     0.11        008-22536178

If you only wanted to see agent_code, agent_name, and commission from the agents who gets the commission less than .15% , the following SQL can be used.

SELECT * FROM agents WHERE commission<.15;

Here is the result for the above statement.

sqlite> SELECT agent_code, agent_name, commission  FROM agents WHERE commission<.15;
AGENT_CODE  AGENT_NAME                                COMMISSION
----------  ----------------------------------------  ----------
A003        Alex                                      0.13
A008        Alford                                    0.12
A010        Santakumar                                0.14
A012        Lucida                                    0.12
A005        Anderson                                  0.13
A001        Subbarao                                  0.14
A002        Mukesh                                    0.11
A009        Benjamin                                  0.11

If you only wanted to see agent_code, agent_name, and commission from the agents who gets the commission more than .12% , the following SQL can be used.

SELECT agent_code, agent_name, commission
FROM agents 
WHERE commission>.12;

Here is the result for the above statement.

sqlite> SELECT agent_code, agent_name, commission
   ...> FROM agents WHERE commission>.12;
AGENT_CODE  AGENT_NAME  COMMISSION
----------  ----------  ----------
A007        Ramasundar  0.15
A003        Alex        0.13
A011        Ravi Kumar  0.15
A010        Santakumar  0.14
A005        Anderson    0.13
A001        Subbarao    0.14
A006        McDen       0.15
A004        Ivan        0.15

If you only want to see cities from Canada, the following SQL can be used.

SELECT * 
FROM locations 
WHERE country_id='CA';

Relational Algebra Expression:

Relational Algebra Expression: SQLite Select Query WHERE clause.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Select Query WHERE clause.

Here is the result for the above statement.

sqlite> SELECT * FROM locations WHERE country_id='CA';
location_id  street_address   postal_code  city        state_province  country_id
-----------  ---------------  -----------  ----------  --------------  ----------
1800         147 Spadina Ave  M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood St  YSW 9T2      Whitehorse  Yukon           CA

WHERE with AND and OR

You can using AND and OR with WHERE clause for filter the rows from the tables.

Here is the sample table employees:


If you want to see the employee_id, first_name, last_name, job_id, manager_id and departments who belongs to the department_id 80, the following SQL can be used.

SELECT employee_id, first_name, last_name,job_id, manager_id,department_id 
FROM employees
WHERE department_id=80;

Relational Algebra Expression:

Relational Algebra Expression: SQLite Select Query WHERE with AND and OR.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Select Query WHERE with AND and OR.

Here is the result.

Sample Output:

employee_id  first_name  last_name   job_id      manager_id  department_id
-----------  ----------  ----------  ----------  ----------  -------------
145          John        Russell     SA_MAN      100         80
146          Karen       Partners    SA_MAN      100         80
147          Alberto     Errazuriz   SA_MAN      100         80
148          Gerald      Cambrault   SA_MAN      100         80
149          Eleni       Zlotkey     SA_MAN      100         80
150          Peter       Tucker      SA_REP      145         80
151          David       Bernstein   SA_REP      145         80
.........

Here in the another example, if you want to see the employee_id, first_name, last_name, job_id, manager_id and departments who belongs to the department_id 80, and must be under the manager whose ID is 100, the following SQL can be used.

SELECT employee_id, first_name, last_name,job_id, manager_id,department_id 
FROM employees
WHERE department_id=80 
AND manager_id=100;

Relational Algebra Expression:

Relational Algebra Expression: SQLite Select Query WHERE with AND and OR.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Select Query WHERE with AND and OR.

Here is the result.

employee_id  first_name  last_name   job_id      manager_id  department_id
-----------  ----------  ----------  ----------  ----------  -------------
145          John        Russell     SA_MAN      100         80
146          Karen       Partners    SA_MAN      100         80
147          Alberto     Errazuriz   SA_MAN      100         80
148          Gerald      Cambrault   SA_MAN      100         80
149          Eleni       Zlotkey     SA_MAN      100         80

Now, lets find those employees, whose job ID may be CLERK or may be in the department which ID is 80 and must be under the manager who is the owner of the ID 147.

Here is the statement.

SELECT employee_id, first_name, last_name,job_id, manager_id,department_id 
FROM employees
WHERE job_id='SH_CLERK' 
OR department_id=80 
AND manager_id=147;

Relational Algebra Expression:

Relational Algebra Expression: SQLite Select Query WHERE with AND and OR.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Select Query WHERE with AND and OR.

Here is the result of the above statement.

employee_id  first_name  last_name   job_id      manager_id  department_id
-----------  ----------  ----------  ----------  ----------  -------------
162          Clara       Vishney     SA_REP      147         80
163          Danielle    Greene      SA_REP      147         80
164          Mattea      Marvins     SA_REP      147         80
165          David       Lee         SA_REP      147         80
166          Sundar      Ande        SA_REP      147         80
167          Amit        Banda       SA_REP      147         80
180          Winston     Taylor      SH_CLERK    120         50
181          Jean        Fleaur      SH_CLERK    120         50
182          Martha      Sullivan    SH_CLERK    120         50
183          Girard      Geoni       SH_CLERK    120         50
184          Nandita     Sarchand    SH_CLERK    121         50
185          Alexis      Bull        SH_CLERK    121         50
.........

The anove result shows that, the 'SA_REP' job_id also appear along with 'SH_CLERK', because the implimentation of OR clause and manager_id 147 appear for only those departments that have the ID 80.

The AND operator has a higher precedence than the OR operator, which means it gets evaluated first. So, in effect, the query really looks like the below:

SELECT employee_id, first_name, last_name,job_id, manager_id,department_id 
FROM employees
WHERE job_id='SH_CLERK' 
OR (department_id=80 AND manager_id=147);

Relational Algebra Expression:

Relational Algebra Expression: SQLite Select Query WHERE with AND and OR.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Select Query WHERE with AND and OR.

Which selects all employees for 'SH_CLERK' and only those employees for department no 80 who have under the manager whose id is 147. The parentheses can be used to clarify the query and actually get the high rollers we wanted.

DISTINCT

The DISTINCT keyword is used to select only unique items from the result set or it can be said that, it eliminates the duplicates rows from the result of the query.

If you want to get the unique department_id and manager_id combination , the following SQL can be used.

SELECT DISTINCT department_id, manager_id
FROM employees;

Relational Algebra Expression:

Relational Algebra Expression: SQLite Select Query DISTINCT.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Select Query DISTINCT.

Here is the result.

department_id  manager_id
-------------  ----------
90
90             100
60             102
60             103
100            101
100            108
30             100
30             114
50             100
50             120
50             121
50             122
50             123
50             124
80             100
80             145
80             146
80             147
80             148
80             149
               149
10             101
20             100
20             201
40             101
70             101
110            101
110            205

The above result shows only the unique combination of department_id and manager_id have appeared. Now, if we add the job_id column with above query, look what will happen.

SELECT DISTINCT department_id, manager_id,job_id
FROM employees;

Relational Algebra Expression:

Relational Algebra Expression: SQLite Select Query DISTINCT.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Select Query DISTINCT.

Here is the output.

department_id  manager_id  job_id
-------------  ----------  ----------
90                         AD_PRES
90             100         AD_VP
60             102         IT_PROG
60             103         IT_PROG
100            101         FI_MGR
100            108         FI_ACCOUNT
30             100         PU_MAN
30             114         PU_CLERK
50             100         ST_MAN
50             120         ST_CLERK
50             121         ST_CLERK
50             122         ST_CLERK
50             123         ST_CLERK
50             124         ST_CLERK
80             100         SA_MAN
80             145         SA_REP
80             146         SA_REP
80             147         SA_REP
80             148         SA_REP
80             149         SA_REP
               149         SA_REP
50             120         SH_CLERK
50             121         SH_CLERK
50             122         SH_CLERK
50             123         SH_CLERK
50             124         SH_CLERK
10             101         AD_ASST
20             100         MK_MAN
20             201         MK_REP
40             101         HR_REP
70             101         PR_REP
110            101         AC_MGR
110            205         AC_ACCOUNT

Here in the above result, the unique combination made by three columns and a naturally number of rows become changed, and here more rows appear than before.

ORDER BY

The ORDER BY clause is used to sort the returned data set. The ORDER BY clause is followed by the column on which we do the sorting. The default direction for ORDER BY is ascending; results are ordered from smallest amount to greatest. To specify the direction of the ORDER BY, use the DESC or ASC keyword:

If you want to sort the result according to salary for those employees which department no. is below 50, the following SQL can be used.

SELECT employee_id, first_name, last_name,job_id, salary,department_id 
FROM employees
WHERE department_id<50 
ORDER BY salary;

Relational Algebra Expression:

Relational Algebra Expression: SQLite Select Query ORDER BY.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Select Query ORDER BY.

Here is the result.

employee_id  first_name  last_name   job_id      salary      department_id
-----------  ----------  ----------  ----------  ----------  -------------
119          Karen       Colmenares  PU_CLERK    2500        30
118          Guy         Himuro      PU_CLERK    2600        30
117          Sigal       Tobias      PU_CLERK    2800        30
116          Shelli      Baida       PU_CLERK    2900        30
115          Alexander   Khoo        PU_CLERK    3100        30
200          Jennifer    Whalen      AD_ASST     4400        10
202          Pat         Fay         MK_REP      6000        20
203          Susan       Mavris      HR_REP      6500        40
114          Den         Raphaely    PU_MAN      11000       30
201          Michael     Hartstein   MK_MAN      13000       20

If you want to sort the result according to salary in descending order for those employees which department no. is below 50 , the following SQL can be used.

SELECT employee_id, first_name, last_name,job_id, salary,department_id 
FROM employees
WHERE department_id<50 
ORDER BY salary DESC;

Relational Algebra Expression:

Relational Algebra Expression: SQLite Select Query ORDER BY.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Select Query ORDER BY.

Here is the result.

employee_id  first_name  last_name   job_id      salary      department_id
-----------  ----------  ----------  ----------  ----------  -------------
201          Michael     Hartstein   MK_MAN      13000       20
114          Den         Raphaely    PU_MAN      11000       30
203          Susan       Mavris      HR_REP      6500        40
202          Pat         Fay         MK_REP      6000        20
200          Jennifer    Whalen      AD_ASST     4400        10
115          Alexander   Khoo        PU_CLERK    3100        30
116          Shelli      Baida       PU_CLERK    2900        30
117          Sigal       Tobias      PU_CLERK    2800        30
118          Guy         Himuro      PU_CLERK    2600        30
119          Karen       Colmenares  PU_CLERK    2500        30

The result of the query can be ordered by more than one column. Rows with the same value for the first column of the ORDER BY are further ordered by the additional column(s):

Here is the example.

If you want to order the result firstly by department_id column in ascending order and after that, which departments are same they will be ordered themselves descendingly according to the salary, the following SQL can be used.

SELECT employee_id, first_name, last_name,job_id, salary,department_id 
FROM employees
WHERE department_id<50 
ORDER BY department_id,salary DESC;

Relational Algebra Expression:

Relational Algebra Expression: SQLite Select Query ORDER BY.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Select Query ORDER BY.

Here is the result.

employee_id  first_name  last_name   job_id      salary      department_id
-----------  ----------  ----------  ----------  ----------  -------------
200          Jennifer    Whalen      AD_ASST     4400        10
201          Michael     Hartstein   MK_MAN      13000       20
202          Pat         Fay         MK_REP      6000        20
114          Den         Raphaely    PU_MAN      11000       30
115          Alexander   Khoo        PU_CLERK    3100        30
116          Shelli      Baida       PU_CLERK    2900        30
117          Sigal       Tobias      PU_CLERK    2800        30
118          Guy         Himuro      PU_CLERK    2600        30
119          Karen       Colmenares  PU_CLERK    2500        30
203          Susan       Mavris      HR_REP      6500        40

It seems to the above result that, depertment_id 20 and 30 have more than one rows, and they have been arranged descendingly according to the salary, that is the greatest salary will come first.

ORDER BY with DISTINCT

If you want to get the unique department_id, manager_id and job_id combination by an order according to department ascendingly, the following SQL can be used.

SELECT DISTINCT department_id, manager_id,job_id
FROM employees ORDER BY department_id;

Relational Algebra Expression:

Relational Algebra Expression: SQLite Select Query ORDER BY with DISTINCT.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Select Query ORDER BY with DISTINCT.

Here is the output.

department_id  manager_id  job_id
-------------  ----------  ----------
10             101         AD_ASST
20             100         MK_MAN
20             201         MK_REP
30             100         PU_MAN
30             114         PU_CLERK
40             101         HR_REP
50             100         ST_MAN
50             120         ST_CLERK
50             121         ST_CLERK
50             122         ST_CLERK
50             123         ST_CLERK
50             124         ST_CLERK
50             120         SH_CLERK
50             121         SH_CLERK
50             122         SH_CLERK
50             123         SH_CLERK
50             124         SH_CLERK
60             102         IT_PROG
60             103         IT_PROG
70             101         PR_REP
80             100         SA_MAN
80             145         SA_REP
80             146         SA_REP
80             147         SA_REP
80             148         SA_REP
80             149         SA_REP
90                         AD_PRES
90             100         AD_VP
100            101         FI_MGR
100            108         FI_ACCOUNT
110            101         AC_MGR
110            205         AC_ACCOUNT
               149         SA_REP

GROUP BY

The GROUP BY clause is used to combine database records with identical values into a single record. It is often used with the aggregation functions.

The GROUP BY clause is used to aggregate data into a single row where the value of one or more specified columns is repeated. It is used to combine database records with identical values into a single record. It is often used with the aggregation functions. The GROUP BY clause takes a list of expressions, usually column names from the result, and aggregates data for each expression. The job of the GROUP BY clause is to compute aggregates over an entire result, and can also split that result into groups of rows with specified values, and compute aggregates on each group, in a single step.

The GROUP BY clause sits in between the WHERE clause and the SELECT clause. It gets the output from WHERE and splits it into groups of rows that share a common value (or values) for a specific column (or columns). These groups are then passed to the SELECT clause.

The grouping process performed by two steps. First, the GROUP BY expression arranged the table rows into different groups. Once the groups are defined, the SELECT header defines how those groups are flattened down into a single row. The resulting table will have one row for each group.

If you want to find the total salary for each job_id, the following SQL can be used.

SELECT job_id as Designation ,SUM(salary) "Total Salary" 
FROM employees
GROUP BY job_id;

Here is the result

Designation  Total Salary
-----------  ------------
AC_ACCOUNT   8300
AC_MGR       12000
AD_ASST      4400
AD_PRES      24000
AD_VP        34000
FI_ACCOUNT   39600
FI_MGR       12000
HR_REP       6500
IT_PROG      28800
MK_MAN       13000
MK_REP       6000
PR_REP       10000
PU_CLERK     13900
PU_MAN       11000
SA_MAN       61000
SA_REP       250500
SH_CLERK     64300
ST_CLERK     55700
ST_MAN       36400

If you want to find the total number of employees against each designation, the following SQL can be used.

SELECT job_id as Designation ,COUNT(*) "Number of Employees" 
FROM employees
GROUP BY job_id;

Here is the result

Designation  Number of Employees
-----------  -------------------
AC_ACCOUNT   1
AC_MGR       1
AD_ASST      1
AD_PRES      1
AD_VP        2
FI_ACCOUNT   5
FI_MGR       1
HR_REP       1
IT_PROG      5
MK_MAN       1
MK_REP       1
PR_REP       1
PU_CLERK     5
PU_MAN       1
SA_MAN       5
SA_REP       30
SH_CLERK     20
ST_CLERK     20
ST_MAN       5

GROUP BY with WHERE

If you want to find the total salary for each job_id which belongs to the department that ID number is below 80 , the following SQL can be used.

SELECT job_id as Designation ,SUM(salary) "Total Salary" 
FROM employees
WHERE department_id<80
GROUP BY job_id;

Here is the result

Designation  Total Salary
-----------  ------------
AD_ASST      4400
HR_REP       6500
IT_PROG      28800
MK_MAN       13000
MK_REP       6000
PR_REP       10000
PU_CLERK     13900
PU_MAN       11000
SH_CLERK     64300
ST_CLERK     55700
ST_MAN       36400

GROUP BY and ORDER BY

If you want to find the total number of employees against each designation by an order of highest to the smallest number of employees, the following SQL can be used.

SELECT job_id as Designation ,COUNT(*) "Number of Employees" 
FROM employees
GROUP BY job_id
ORDER BY COUNT(*) DESC;

Here is the result

Designation  Number of Employees
-----------  -------------------
SA_REP       30
SH_CLERK     20
ST_CLERK     20
FI_ACCOUNT   5
IT_PROG      5
PU_CLERK     5
SA_MAN       5
ST_MAN       5
AD_VP        2
AC_ACCOUNT   1
AC_MGR       1
AD_ASST      1
AD_PRES      1
FI_MGR       1
HR_REP       1
MK_MAN       1
MK_REP       1
PR_REP       1
PU_MAN       1

HAVING

The HAVING clause functionally is identical to the WHERE clause. It is used to restrict records based on the summary value of a grouping. .HAVING clauses should only contain filter expressions that depend on the GROUP BY output. The main difference between the WHERE clause and the HAVING clause is, the HAVING clause is processed after the GROUP BY and SELECT clauses, allowing to filter rows based on the results of any GROUP BY aggregate while the WHERE clause can only reference expressions that do not contain aggregate functions.

Example:

If you want to find the total number of employees against the designations which contain on or above 5 employees, the following SQL can be used.

SELECT job_id as Designation ,COUNT(*) "Number of Employees" 
FROM employees
GROUP BY job_id
HAVING COUNT(*)>=5;

Here is the result

Designation  Number of Employees
-----------  -------------------
FI_ACCOUNT   5
IT_PROG      5
PU_CLERK     5
SA_MAN       5
SA_REP       30
SH_CLERK     20
ST_CLERK     20
ST_MAN       5

HAVING with ORDER BY

If you want to find the total number of employees against the designations which contain on or above 5 employees, and the result comes by an order of largest to samalles employee number, the following SQL can be used.

SELECT job_id as Designation ,COUNT(*) "Number of Employees" 
FROM employees
GROUP BY job_id
HAVING COUNT(*)>=5
ORDER BY COUNT(*) DESC;

Here is the result

Designation  Number of Employees
-----------  -------------------
SA_REP       30
SH_CLERK     20
ST_CLERK     20
FI_ACCOUNT   5
IT_PROG      5
PU_CLERK     5
SA_MAN       5
ST_MAN       5

HAVING with WHERE

If you want to find the total salary required for a month, which is on or above one lacs, for the designation that holding the ID is below 80, by an order of largest to smallest salary the following SQL can be used.

SELECT job_id as Designation ,SUM(salary) "Total Salary" 
FROM employees
WHERE department_id<80
GROUP BY job_id
HAVING SUM(salary)>=10000
ORDER BY SUM(salary);

Here is the result

Designation  Total Salary
-----------  ------------
PR_REP       10000
PU_MAN       11000
MK_MAN       13000
PU_CLERK     13900
IT_PROG      28800
ST_MAN       36400
ST_CLERK     55700
SH_CLERK     64300

Previous: Create, Alter, Drop index
Next: SQLite Operators Introduction



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/sqlite/sqlite-select-query-statement.php