SELECT Examples
A. Use SELECT to retrieve rows and columns
This example shows three code examples. This first code example returns all rows (no WHERE clause is specified) and all columns (using the *) from the authors table in the pubs database.
USE pubs
SELECT *
FROM authors
ORDER BY au_lname ASC, au_fname ASC
-- Alternate way.
USE pubs
SELECT authors.*
FROM customers
ORDER BY au_lname ASC, au_fname ASC
This example returns all rows (no WHERE clause is specified), and only a subset of the columns (au_lname, au_fname, phone, city, state) from the authors table in the pubs database. In addition, column headings are added.
USE pubs
SELECT au_fname, au_lname, phone AS Telephone, city, state
FROM authors
ORDER BY au_lname ASC, au_fname ASC
This example returns only the rows for authors who live in California and do not have the last name McBadden.
USE pubs
SELECT au_fname, au_lname, phone AS Telephone
FROM authors
WHERE state = 'CA' and au_lname <> 'McBadden'
ORDER BY au_lname ASC, au_fname ASC
B. Use SELECT with column headings and calculations
These examples return all rows from titles. The first example returns total year-to-date sales and the amounts due to each author and publisher. In the second example, the total revenue is calculated for each book.
USE pubs
SELECT ytd_sales AS Sales,
authors.au_fname + ' '+ authors.au_lname AS Author,
ToAuthor = (ytd_sales * royalty) / 100,
ToPublisher = ytd_sales - (ytd_sales * royalty) / 100
FROM titles INNER JOIN titleauthor
ON titles.title_id = titleauthor.title_id INNER JOIN authors
ON titleauthor.au_id = authors.au_id
ORDER BY Sales DESC, Author ASC
Here is the result set:
Sales Author ToAuthor ToPublisher
----------- ------------------------- ----------- -----------
22246 Anne Ringer 5339 16907
22246 Michel DeFrance 5339 16907
18722 Marjorie Green 4493 14229
15096 Reginald Blotchet-Halls 2113 12983
8780 Cheryl Carson 1404 7376
4095 Abraham Bennet 409 3686
4095 Akiko Yokomoto 409 3686
4095 Ann Dull 409 3686
4095 Burt Gringlesby 409 3686
4095 Dean Straight 409 3686
4095 Marjorie Green 409 3686
4095 Michael O'Leary 409 3686
4095 Sheryl Hunter 409 3686
4072 Johnson White 407 3665
3876 Michael O'Leary 387 3489
3876 Stearns MacFeather 387 3489
3336 Charlene Locksley 333 3003
2045 Albert Ringer 245 1800
2045 Anne Ringer 245 1800
2032 Innes del Castillo 243 1789
375 Livia Karsen 37 338
375 Stearns MacFeather 37 338
375 Sylvia Panteley 37 338
111 Albert Ringer 11 100
NULL Charlene Locksley NULL NULL
(25 row(s) affected)
This is the query that calculates the revenue for each book:
USE pubs
SELECT 'Total income is', price * ytd_sales AS Revenue,
'for', title_id AS Book#
FROM titles
ORDER BY Book# ASC
Here is the result set:
Revenue Book#
--------------- --------------------- ---- ------
Total income is 81859.0500 for BU1032
Total income is 46318.2000 for BU1111
Total income is 55978.7800 for BU2075
Total income is 81859.0500 for BU7832
Total income is 40619.6800 for MC2222
Total income is 66515.5400 for MC3021
Total income is NULL for MC3026
Total income is 201501.0000 for PC1035
Total income is 81900.0000 for PC8888
Total income is NULL for PC9999
Total income is 8096.2500 for PS1372
Total income is 22392.7500 for PS2091
Total income is 777.0000 for PS2106
Total income is 81399.2800 for PS3333
Total income is 26654.6400 for PS7777
Total income is 7856.2500 for TC3218
Total income is 180397.2000 for TC4203
Total income is 61384.0500 for TC7777
(18 row(s) affected)
C. Use DISTINCT with SELECT
This example uses DISTINCT to prevent the retrieval of duplicate author ID numbers.
USE pubs
SELECT DISTINCT au_id
FROM authors
ORDER BY au_id
D. Create tables with SELECT INTO
This first example creates a temporary table named #coffeetabletitles in tempdb. To use this table, always refer to it with the exact name shown, including the number sign (#).
USE pubs
DROP TABLE #coffeetabletitles
GO
SET NOCOUNT ON
SELECT * INTO #coffeetabletitles
FROM titles
WHERE price < $20
SET NOCOUNT OFF
SELECT name
FROM tempdb..sysobjects
WHERE name LIKE '#c%'
Here is the result set:
name
------------------------------------------------------------------------
#coffeetabletitles__________________________________________________________________________________________________000000000028
(1 row(s) affected)
CHECKPOINTing database that was changed.
(12 row(s) affected)
name
------------------------------------------------------------------------
newtitles
(1 row(s) affected)
CHECKPOINTing database that was changed.
This second example creates a permanent table named newtitles.
USE pubs
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'newtitles')
DROP TABLE newtitles
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
USE pubs
SELECT * INTO newtitles
FROM titles
WHERE price > $25 OR price < $20
SELECT name FROM sysobjects WHERE name LIKE 'new%'
USE master
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
Here is the result set:
name
------------------------------
newtitles
(1 row(s) affected)
E. Use correlated subqueries
This example shows queries that are semantically equivalent and illustrates the difference between using the EXISTS keyword and the IN keyword. Both are examples of a valid subquery retrieving one instance of each publisher name for which the book title is a business book, and the publisher ID numbers match between the titles and publishers tables.
USE pubs SELECT DISTINCT pub_name FROM publishers WHERE EXISTS(SELECT *FROM titlesWHERE pub_id = publishers.pub_idAND type = 'business')-- OrUSE pubsSELECT distinct pub_nameFROM publishersWHERE pub_id IN(SELECT pub_idFROM titlesWHERE type = 'business')
This example uses IN in a correlated (or repeating) subquery, which is a query that depends on the outer query for its values. It is executed repeatedly, once for each row that may be selected by the outer query. This query retrieves one instance of each author's first and last name for which the royalty percentage in the titleauthor table is 100 and for which the author identification numbers match in the authors and titleauthor tables.
USE pubs SELECT DISTINCT au_lname, au_fname FROM authors WHERE 100 IN(SELECT royaltyperFROM titleauthorWHERE titleauthor.au_id = authors.au_id)
The above subquery in this statement cannot be evaluated independently of the outer query. It needs a value for authors.au_id, but this value changes as Microsoft® SQL Server™ examines different rows in authors.
A correlated subquery can also be used in the HAVING clause of an outer query. This example finds the types of books for which the maximum advance is more than twice the average for the group.
USE pubs SELECT t1.type FROM titles t1 GROUP BY t1.type HAVING MAX(t1.advance) >= ALL(SELECT 2 * AVG(t2.advance)FROM titles t2WHERE t1.type = t2.type)
This example uses two correlated subqueries to find the names of authors who have participated in writing at least one popular computing book.
USE pubs SELECT au_lname, au_fname FROM authors WHERE au_id IN(SELECT au_idFROM titleauthorWHERE title_id IN(SELECT title_idFROM titlesWHERE type = 'popular_comp'))
F. Use GROUP BY
This example finds the total year-to-date sales of each publisher in the database.
USE pubs
SELECT pub_id, SUM(ytd_sales) AS total
FROM titles
GROUP BY pub_id
ORDER BY pub_id
Here is the result set:
pub_idtotal-----------073628286087744219138924941(3 row(s) affected)
Because of the GROUP BY clause, only one row containing the sum of all sales is returned for each publisher.
G. Use GROUP BY with multiple groups
This example finds the average price and the sum of year-to-date sales, grouped by type and publisher ID.
USE pubs
SELECT type, pub_id, AVG(price) AS 'avg', sum(ytd_sales) AS 'sum'
FROM titles
GROUP BY type, pub_id
ORDER BY type, pub_id
Here is the result set:
type pub_id avg sum
------------ ------ --------------------- -----------
business 0736 2.9900 18722
business 1389 17.3100 12066
mod_cook 0877 11.4900 24278
popular_comp 1389 21.4750 12875
psychology 0736 11.4825 9564
psychology 0877 21.5900 375
trad_cook 0877 15.9633 19566
UNDECIDED 0877 NULL NULL
(8 row(s) affected)
Warning, null value eliminated from aggregate.
H. Use GROUP BY and WHERE
This example puts the results into groups after retrieving only the rows with advances greater than $5,000.
USE pubs
SELECT type, AVG(price)
FROM titles
WHERE advance > $5000
GROUP BY type
ORDER BY type
Here is the result set:
type
------------ --------------------------
business 2.99
mod_cook 2.99
popular_comp 21.48
psychology 14.30
trad_cook 17.97
(5 row(s) affected)
I. Use GROUP BY with an expression
This example groups by an expression. You can group by an expression if the expression does not include aggregate functions.
USE pubs
SELECT AVG(ytd_sales), ytd_sales * royalty
FROM titles
GROUP BY ytd_sales * royalty
ORDER BY ytd_sales * royalty
Here is the result set:
----------- -----------
NULL NULL
111 1110
375 3750
2032 24384
2045 24540
3336 33360
3876 38760
4072 40720
4095 40950
8780 140480
15096 211344
18722 449328
22246 533904
(13 row(s) affected)
J. Compare GROUP BY and GROUP BY ALL
The first example produces groups only for those books that commanded royalties of 10 percent. Because no modern cookbooks have a royalty of 10 percent, there is no group in the results for the mod_cook type.
The second example produces groups for all types, including modern cookbooks and UNDECIDED, although the modern cookbook group does not include any rows that meet the qualification specified in the WHERE clause.
The column that holds the aggregate value (the average price) is NULL for groups that lack qualifying rows.
USE pubs
SELECT type, AVG(price)
FROM titles
WHERE royalty = 10
GROUP BY type
ORDER BY type
Here is the result set:
type
------------ --------------------------
business 17.31
popular_comp 20.00
psychology 14.14
trad_cook 17.97
(4 row(s) affected)
-- Using GROUP BY ALL
USE pubs
SELECT type, AVG(price)
FROM titles
WHERE royalty = 10
GROUP BY all type
ORDER BY type
Here is the result set:
type
------------ --------------------------
business 17.31
mod_cook NULL
popular_comp 20.00
psychology 14.14
trad_cook 17.97
UNDECIDED NULL
(6 row(s) affected)
K. Use GROUP BY with ORDER BY
This example finds the average price of each type of book and orders the results by average price.
USE pubs
SELECT type, AVG(price)
FROM titles
GROUP BY type
ORDER BY AVG(price)
Here is the result set:
type
------------ --------------------------
UNDECIDED NULL
mod_cook 11.49
psychology 13.50
business 13.73
trad_cook 15.96
popular_comp 21.48
(6 row(s) affected)
L. Use the HAVING clause
The first example shows a HAVING clause with an aggregate function. It groups the rows in the titles table by type and eliminates the groups that include only one book. The second example shows a HAVING clause without aggregate functions. It groups the rows in the titles table by type and eliminates those types that do not start with the letter p.
USE pubs
SELECT type
FROM titles
GROUP BY type
HAVING COUNT(*) > 1
ORDER BY type
Here is the result set:
type
------------
business
mod_cook
popular_comp
psychology
trad_cook
(5 row(s) affected)
This query uses the LIKE clause in the HAVING clause.
USE pubs
SELECT type
FROM titles
GROUP BY type
HAVING type LIKE 'p%'
ORDER BY type
Here is the result set:
type
------------
popular_comp
psychology
(2 row(s) affected)
M. Use HAVING and GROUP BY
This example shows using GROUP BY, HAVING, WHERE, and ORDER BY clauses in one SELECT statement. It produces groups and summary values but does so after eliminating the titles with prices under $5. It also organizes the results by pub_id.
USE pubs SELECT pub_id, SUM(advance), AVG(price) FROM titles WHERE price >= $5 GROUP BY pub_id HAVING SUM(advance) > $15000AND AVG(price) < $20AND pub_id > '0800'ORDER BY pub_id
Here is the result set:
pub_id
------ -------------------------- --------------------------
0877 26,000.00 17.89
1389 30,000.00 18.98
(2 row(s) affected)
N. Use HAVING with SUM and AVG
This example groups the titles table by publisher and includes only those groups of publishers who have paid more than $25,000 in total advances and whose books average more than $15 in price.
USE pubs
SELECT pub_id, SUM(advance), AVG(price)
FROM titles
GROUP BY pub_id
HAVING SUM(advance) > $25000
AND AVG(price) > $15
To see the publishers who have had year-to-date sales greater than $40,000, use this query:
USE pubs
SELECT pub_id, total = SUM(ytd_sales)
FROM titles
GROUP BY pub_id
HAVING SUM(ytd_sales) > 40000
If you want to make sure there are at least six books involved in the calculations for each publisher, use HAVING COUNT(*) > 5 to eliminate the publishers that return totals for fewer than six books. The query looks like this:
USE pubs
SELECT pub_id, SUM(ytd_sales) AS total
FROM titles
GROUP BY pub_id
HAVING COUNT(*) > 5
Here is the result set:
pub_id total
------ -----
0877 44219
1389 24941
(2 row(s) affected)
With this statement, two rows are returned. New Moon Books (0736) is eliminated.
O. Calculate group totals with COMPUTE BY
This example uses two code examples to show the use of COMPUTE BY. The first code example uses one COMPUTE BY with one aggregate function, and the second code example uses one COMPUTE BY item and two aggregate functions.
This example calculates the sum of the prices (for prices over $10) for each type of cookbook, in order first by type of book and then by price of book.
USE pubs
SELECT type, price
FROM titles
WHERE price > $10
AND type LIKE '%cook'
ORDER BY type, price
COMPUTE SUM(price) BY type
Here is the result set:
type price
------------ ---------------------
mod_cook 19.9900
(1 row(s) affected)
sum
---------------------
19.9900
(1 row(s) affected)
type price
------------ ---------------------
trad_cook 11.9500
trad_cook 14.9900
trad_cook 20.9500
(3 row(s) affected)
sum
---------------------
47.8900
(1 row(s) affected)
This example retrieves the book type, publisher identification number, and price of all cookbooks. The COMPUTE BY clause uses two different aggregate functions.
USE pubs
SELECT type, pub_id, price
FROM titles
WHERE type LIKE '%cook'
ORDER BY type, pub_id
COMPUTE SUM(price), MAX(pub_id) BY type
Here is the result set:
type pub_id price
------------ ------ ---------------------
mod_cook 0877 19.9900
mod_cook 0877 2.9900
(2 row(s) affected)
sum max
--------------------- ----
22.9800 0877
(1 row(s) affected)
type pub_id price
------------ ------ ---------------------
trad_cook 0877 20.9500
trad_cook 0877 11.9500
trad_cook 0877 14.9900
(3 row(s) affected)
sum max
--------------------- ----
47.8900 0877
(1 row(s) affected)
P. Calculate grand values using COMPUTE without BY
The COMPUTE keyword can be used without BY to generate grand totals, grand counts, and so on.
This statement finds the grand total of the prices and advances for all types of books over $20.
USE pubs
SELECT type, price, advance
FROM titles
WHERE price > $20
COMPUTE SUM(price), SUM(advance)
You can use COMPUTE BY and COMPUTE without BY in the same query. This query finds the sum of prices and advances by type, and then computes the grand total of prices and advances for all types of books.
USE pubs
SELECT type, price, advance
FROM titles
WHERE type LIKE '%cook'
ORDER BY type, price
COMPUTE SUM(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)
Here is the result set:
type price advance
------------ --------------------- ---------------------
mod_cook 2.9900 15000.0000
mod_cook 19.9900 .0000
(2 row(s) affected)
sum sum
--------------------- ---------------------
22.9800 15000.0000
(1 row(s) affected)
type price advance
------------ --------------------- ---------------------
trad_cook 11.9500 4000.0000
trad_cook 14.9900 8000.0000
trad_cook 20.9500 7000.0000
(3 row(s) affected)
sum sum
--------------------- ---------------------
47.8900 19000.0000
(1 row(s) affected)
sum sum
--------------------- ---------------------
70.8700 34000.0000
(1 row(s) affected)
Q. Calculate computed sums on all rows
This example shows only three columns in the select list and gives totals based on all prices and all advances at the end of the results.
USE pubs
SELECT type, price, advance
FROM titles
COMPUTE SUM(price), SUM(advance)
Here is the result set:
type price advance
------------ --------------------- ---------------------
business 19.9900 5000.0000
business 11.9500 5000.0000
business 2.9900 10125.0000
business 19.9900 5000.0000
mod_cook 19.9900 .0000
mod_cook 2.9900 15000.0000
UNDECIDED NULL NULL
popular_comp 22.9500 7000.0000
popular_comp 20.0000 8000.0000
popular_comp NULL NULL
psychology 21.5900 7000.0000
psychology 10.9500 2275.0000
psychology 7.0000 6000.0000
psychology 19.9900 2000.0000
psychology 7.9900 4000.0000
trad_cook 20.9500 7000.0000
trad_cook 11.9500 4000.0000
trad_cook 14.9900 8000.0000
(18 row(s) affected)
sum sum
--------------------- ---------------------
236.2600 95400.0000
(1 row(s) affected)
Warning, null value eliminated from aggregate.
R. Use more than one COMPUTE clause
This example finds the sum of the prices of all psychology books, as well as the sum of the prices of psychology books organized by publisher. You can use different aggregate functions in the same statement by including more than one COMPUTE BY clause.
USE pubs
SELECT type, pub_id, price
FROM titles
WHERE type = 'psychology'
ORDER BY type, pub_id, price
COMPUTE SUM(price) BY type, pub_id
COMPUTE SUM(price) BY type
Here is the result set:
type pub_id price
------------ ------ ---------------------
psychology 0736 7.0000
psychology 0736 7.9900
psychology 0736 10.9500
psychology 0736 19.9900
(4 row(s) affected)
sum
---------------------
45.9300
(1 row(s) affected)
type pub_id price
------------ ------ ---------------------
psychology 0877 21.5900
(1 row(s) affected)
sum
---------------------
21.5900
(1 row(s) affected)
sum
---------------------
67.5200
(1 row(s) affected)
S. Compare GROUP BY with COMPUTE
The first example uses the COMPUTE clause to calculate the sum for the prices of the different types of cookbooks. The second example produces the same summary information using only GROUP BY.
USE pubs
-- Using COMPUTE
SELECT type, price
FROM titles
WHERE type like '%cook'
ORDER BY type, price
COMPUTE SUM(price) BY type
Here is the result set:
type price
------------ ---------------------
mod_cook 2.9900
mod_cook 19.9900
(2 row(s) affected)
sum
---------------------
22.9800
(1 row(s) affected)
type price
------------ ---------------------
trad_cook 11.9500
trad_cook 14.9900
trad_cook 20.9500
(3 row(s) affected)
sum
---------------------
47.8900
(1 row(s) affected)
This is the second query using GROUP BY:
USE pubs
-- Using GROUP BY
SELECT type, SUM(price)
FROM titles
WHERE type LIKE '%cook'
GROUP BY type
ORDER BY type
Here is the result set:
type
------------ ---------------------
mod_cook 22.9800
trad_cook 47.8900
(2 row(s) affected)
T. Use SELECT with GROUP BY, COMPUTE, and ORDER BY clauses
This example returns only those rows with current year-to-date sales, and then computes the average book cost and total advances in descending order by type. Four columns of data are returned, including a truncated title. All computed columns appear within the select list.
USE pubs
SELECT CAST(title AS char(20)) AS title, type, price, advance
FROM titles
WHERE ytd_sales IS NOT NULL
ORDER BY type DESC
COMPUTE AVG(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)
Here is the result set:
title type price advance
-------------------- ------------ --------------------- ----------------
Onions, Leeks, and G trad_cook 20.9500 7000.0000
Fifty Years in Bucki trad_cook 11.9500 4000.0000
Sushi, Anyone? trad_cook 14.9900 8000.0000
(3 row(s) affected)
avg sum
--------------------- ---------------------
15.9633 19000.0000
(1 row(s) affected)
title type price advance
-------------------- ------------ --------------------- ----------------
Computer Phobic AND psychology 21.5900 7000.0000
Is Anger the Enemy? psychology 10.9500 2275.0000
Life Without Fear psychology 7.0000 6000.0000
Prolonged Data Depri psychology 19.9900 2000.0000
Emotional Security: psychology 7.9900 4000.0000
(5 row(s) affected)
avg sum
--------------------- ---------------------
13.5040 21275.0000
(1 row(s) affected)
title type price advance
-------------------- ------------ --------------------- ----------------
But Is It User Frien popular_comp 22.9500 7000.0000
Secrets of Silicon V popular_comp 20.0000 8000.0000
(2 row(s) affected)
avg sum
--------------------- ---------------------
21.4750 15000.0000
(1 row(s) affected)
title type price advance
-------------------- ------------ --------------------- ----------------
Silicon Valley Gastr mod_cook 19.9900 .0000
The Gourmet Microwav mod_cook 2.9900 15000.0000
(2 row(s) affected)
avg sum
--------------------- ---------------------
11.4900 15000.0000
(1 row(s) affected)
title type price advance
-------------------- ------------ --------------------- ----------------
The Busy Executive's business 19.9900 5000.0000
Cooking with Compute business 11.9500 5000.0000
You Can Combat Compu business 2.9900 10125.0000
Straight Talk About business 19.9900 5000.0000
(4 row(s) affected)
avg sum
--------------------- ---------------------
13.7300 25125.0000
(1 row(s) affected)
sum sum
--------------------- ---------------------
236.2600 95400.0000
(1 row(s) affected)
U. Use SELECT statement with CUBE
This example shows two code examples. The first example returns a result set from a SELECT statement using the CUBE operator. The SELECT statement covers a one-to-many relationship between book titles and the quantity sold of each book. By using the CUBE operator, the statement returns an extra row.
USE pubs
SELECT SUBSTRING(title, 1, 65) AS title, SUM(qty) AS 'qty'
FROM sales INNER JOIN titles
ON sales.title_id = titles.title_id
GROUP BY title
WITH CUBE
ORDER BY title
Here is the result set:
title qty
----------------------------------------------------------------- ------
NULL 493
But Is It User Friendly? 30
Computer Phobic AND Non-Phobic Individuals: Behavior Variations 20
Cooking with Computers: Surreptitious Balance Sheets 25
...
The Busy Executive's Database Guide 15
The Gourmet Microwave 40
You Can Combat Computer Stress! 35
(17 row(s) affected)
NULL represents all values in the title column. The result set returns values for the quantity sold of each title and the total quantity sold of all titles. Applying the CUBE operator or ROLLUP operator returns the same result.
This example uses the cube_examples table to show how the CUBE operator affects the result set and uses an aggregate function (SUM). The cube_examples table contains a product name, a customer name, and the number of orders each customer has made for a particular product.
USE pubs
CREATE TABLE cube_examples
(product_name varchar(30) NULL,
customer_name varchar(30) NULL,
number_of_orders int NULL
)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Outback Lager', 'Wilman Kala', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Romero y tomillo', 20)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Wilman Kala', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Outback Lager', 'Wilman Kala', 20)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Wilman Kala', 30)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Eastern Connection', 40)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Outback Lager', 'Eastern Connection', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Wilman Kala', 40)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Romero y tomillo', 50)
First, issue a typical query with a GROUP BY clause and the result set.
USE pubs
SELECT product_name, customer_name, SUM(number_of_orders)
FROM cube_examples
GROUP BY product_name, customer_name
ORDER BY product_name
The GROUP BY causes the result set to form groups within groups. Here is the result set:
product_name customer_name
------------------------------ ------------------------------ ----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala 30
Ikura Romero y tomillo 20
Ikura Wilman Kala 50
Outback Lager Eastern Connection 10
Outback Lager Wilman Kala 30
(7 row(s) affected)
Next, issue a query with a GROUP BY clause by using the CUBE operator. The result set should include the same information, and super-aggregate information for each of the GROUP BY columns.
USE pubs
SELECT product_name, customer_name, SUM(number_of_orders)
FROM cube_examples
GROUP BY product_name, customer_name
WITH CUBE
The result set for the CUBE operator holds the values from the simple GROUP BY result set above, and adds the super-aggregates for each column in the GROUP BY clause. NULL represents all values in the set from which the aggregate is computed. Here is the result set:
product_name customer_name
------------------------------ ------------------------------ ----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala 30
Filo Mix NULL 150
Ikura Romero y tomillo 20
Ikura Wilman Kala 50
Ikura NULL 70
Outback Lager Eastern Connection 10
Outback Lager Wilman Kala 30
Outback Lager NULL 40
NULL NULL 260
NULL Eastern Connection 50
NULL Romero y tomillo 100
NULL Wilman Kala 110
(14 row(s) affected)
Line 4 of the result set indicates that a total of 150 orders for Filo Mix was placed for all customers.
Line 11 of the result set indicates that the total number of orders placed for all products by all customers is 260.
Lines 12-14 of the result set indicate that the total number of orders for each customer for all products are 100, 110, and 50, respectively.
V. Use CUBE on a result set with three columns
This example shows two code examples. The first code example produces a CUBE result set with three columns, and the second example produces a four-column CUBE result set.
The first SELECT statement returns the publication name, title, and quantity of books sold. The GROUP BY clause in this example includes two columns called pub_name and title. There are also two one-to-many relationships between publishers and titles and between titles and sales.
By using the CUBE operator, the result set contains more detailed information about the quantities of titles sold by publishers. NULL represents all values in the title column.
USE pubs
SELECT pub_name, title, SUM(qty) AS 'qty'
FROM sales INNER JOIN titles
ON sales.title_id = titles.title_id INNER JOIN publishers
ON publishers.pub_id = titles.pub_id
GROUP BY pub_name, title
WITH CUBE
Here is the result set:
pub_name title qty
-------------------- ---------------------------------------- ------
Algodata Infosystems But Is It User Friendly? 30
Algodata Infosystems Cooking with Computers: Surreptitious Ba 25
Algodata Infosystems Secrets of Silicon Valley 50
Algodata Infosystems Straight Talk About Computers 15
Algodata Infosystems The Busy Executive's Database Guide 15
Algodata Infosystems NULL 135
Binnet & Hardley Computer Phobic AND Non-Phobic Individu 20
Binnet & Hardley Fifty Years in Buckingham Palace Kitche 20
... ...
NULL Sushi, Anyone? 20
NULL The Busy Executive's Database Guide 15
NULL The Gourmet Microwave 40
NULL You Can Combat Computer Stress! 35
(36 row(s) affected)
Increasing the number of columns in the GROUP BY clause shows why the CUBE operator is an n-dimensional operator. A GROUP BY clause with two columns returns three more kinds of groupings when the CUBE operator is used. The number of groupings can be more than three, depending on the distinct values in the columns.
The result set is grouped by the publisher name and then by the book title. The quantity of each title sold by each publisher is listed in the right-hand column.
NULL in the title column represents all titles. For more information about how to differentiate specific values and all values in the result set, see Example H. The CUBE operator returns these groups of information from one SELECT statement:
- Quantity of each title that each publisher has sold
- Quantity of each title sold
- Quantity of titles sold by each publisher
- Total number of titles sold by all publishers
Each column referenced in the GROUP BY clause has been cross-referenced with all other columns in the GROUP BY clause and the SUM aggregate has been reapplied, which produces additional rows in the result set. Information returned in the result set grows n-dimensionally along with the number of columns in the GROUP BY clause.
Note Ensure that the columns following the GROUP BY clause have meaningful, real-life relationships with each other. For example, if you use au_fname and au_lname, the CUBE operator returns irrelevant information, such as the number of books sold by authors with the same first name. Using the CUBE operator on a real-life hierarchy, such as yearly sales and quarterly sales, produces meaningless rows in the result set. It is more efficient to use the ROLLUP operator.
In this second code example, the GROUP BY clause contains three columns cross-referenced by the CUBE operator. Three one-to-many relationships exist between publishers and authors, between authors and titles, and between titles and sales.
By using the CUBE operator, more detailed information is returned about the quantities of titles sold by publishers.
USE pubs
SELECT pub_name, au_lname, title, SUM(qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titles.title_id = titleauthor.title_id INNER JOIN publishers
ON publishers.pub_id = titles.pub_id INNER JOIN sales
ON sales.title_id = titles.title_id
GROUP BY pub_name, au_lname, title
WITH CUBE
The CUBE operator returns this information based on the cross-referenced groupings returned with the CUBE operator:
- Quantity of each title that each publisher has sold for each author
- Quantity of all titles each publisher has sold for each author
- Quantity of all titles each publisher has sold
- Total quantity of all titles sold by all publishers for all authors
- Quantity of each title sold by all publishers for each author
- Quantity of all titles sold by all publishers for each author
- Quantity of each title sold by each publisher for all authors
- Quantity of each title sold by all publishers for each author
Note The super-aggregate for all publishers, all titles, and all authors is greater than the total number of sales, because a number of books have more than one author.
A pattern emerges as the number of relationships grow. The pattern of values and NULL in the report shows which groups have been formed for a summary aggregate. Explicit information about the groups is provided by the GROUPING function.
W. Use the GROUPING function with CUBE
This example shows how the SELECT statement uses the SUM aggregate, the GROUP BY clause, and the CUBE operator. It also uses the GROUPING function on the two columns listed after the GROUP BY clause.
USE pubs
SELECT pub_name, GROUPING(pub_name),title, GROUPING(title),
SUM(qty) AS 'qty'
FROM sales INNER JOIN titles
ON sales.title_id = titles.title_id INNER JOIN publishers
ON publishers.pub_id = titles.pub_id
GROUP BY pub_name, title
WITH CUBE
The result set has two columns containing 0 and 1 values, which are produced by the GROUPING(pub_name) and GROUPING(title) expressions.
Here is the result set:
pub_name title qty
-------------------- --- ------------------------- --- -----------
Algodata Infosystems 0 But Is It User Friendly? 0 30
Algodata Infosystems 0 Cooking with Computers: S 0 25
Algodata Infosystems 0 Secrets of Silicon Valley 0 50
Algodata Infosystems 0 Straight Talk About Compu 0 15
Algodata Infosystems 0 The Busy Executive's Data 0 15
Algodata Infosystems 0 NULL 1 135
Binnet & Hardley 0 Computer Phobic AND Non-P 0 20
Binnet & Hardley 0 Fifty Years in Buckingham 0 20
... ...
NULL 1 The Busy Executive's Data 0 15
NULL 1 The Gourmet Microwave 0 40
NULL 1 You Can Combat Computer S 0 35
(36 row(s) affected)
X. Use the ROLLUP operator
This example shows two code examples. This first example retrieves the product name, customer name, and the sum of orders placed and uses the ROLLUP operator.
USE pubs
SELECT product_name, customer_name, SUM(number_of_orders)
AS 'Sum orders'
FROM cube_examples
GROUP BY product_name, customer_name
WITH ROLLUP
Here is the result set:
product_name customer_name Sum orders
------------------------------ ------------------------------ ----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala 30
Filo Mix NULL 150
Ikura Romero y tomillo 20
Ikura Wilman Kala 50
Ikura NULL 70
Outback Lager Eastern Connection 10
Outback Lager Wilman Kala 30
Outback Lager NULL 40
NULL NULL 260
(11 row(s) affected)
This second example performs a ROLLUP operation on the company and department columns and totals the number of employees.
The ROLLUP operator produces a summary of aggregates. This is useful when summary information is needed but a full CUBE provides extraneous data or when you have sets within sets. For example, departments within a company are a set within a set.
USE pubs
CREATE TABLE personnel
(
company_name varchar(20),
department varchar(15),
num_employees int
)
INSERT personnel VALUES ('Du monde entier', 'Finance', 10)
INSERT personnel VALUES ('Du monde entier', 'Engineering', 40)
INSERT personnel VALUES ('Du monde entier', 'Marketing', 40)
INSERT personnel VALUES ('Piccolo und mehr', 'Accounting', 20)
INSERT personnel VALUES ('Piccolo und mehr', 'Personnel', 30)
INSERT personnel VALUES ('Piccolo und mehr', 'Payroll', 40)
In this query, the company name, department, and the sum of all employees for the company become part of the result set, in addition to the ROLLUP calculations.
SELECT company_name, department, SUM(num_employees)
FROM personnel
GROUP BY company_name, department WITH ROLLUP
Here is the result set:
company_name department
-------------------- --------------- -----------
Du monde entier Engineering 40
Du monde entier Finance 10
Du monde entier Marketing 40
Du monde entier NULL 90
Piccolo und mehr Accounting 20
Piccolo und mehr Payroll 40
Piccolo und mehr Personnel 30
Piccolo und mehr NULL 90
NULL NULL 180
(9 row(s) affected)
Y. Use the GROUPING function
This example adds three new rows to the cube_examples table. Each of the three records NULL in one or more columns to show only the ROLLUP function produces a value of 1 in the grouping column. In addition, this example modifies the SELECT statement that was used in the earlier example.
USE pubs
-- Add first row with a NULL customer name and 0 orders.
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', NULL, 0)
-- Add second row with a NULL product and NULL customer with real value
-- for orders.
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES (NULL, NULL, 50)
-- Add third row with a NULL product, NULL order amount, but a real
-- customer name.
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES (NULL, 'Wilman Kala', NULL)
SELECT product_name AS Prod, customer_name AS Cust,
SUM(number_of_orders) AS 'Sum Orders',
GROUPING(product_name) AS 'Grp prod_name',
GROUPING(customer_name) AS 'Grp cust_name'
FROM cube_examples
GROUP BY product_name, customer_name
WITH ROLLUP
The GROUPING function can be used only with CUBE or ROLLUP. The GROUPING function returns 1 when an expression evaluates to NULL, because the column value is NULL and represents the set of all values. The GROUPING function returns 0 when the corresponding column (whether it is NULL or not) did not come from either the CUBE or ROLLUP options as a syntax value. The returned value has a tinyint data type.
Here is the result set:
Prod Cust Sum Orders Grp prod_name Grp cust_name
------------- ------------------ ----------- ------------- -------------
NULL NULL 50 0 0
NULL Wilman Kala NULL 0 0
NULL NULL 50 0 1
Filo Mix Eastern Connection 40 0 0
Filo Mix Romero y tomillo 80 0 0
Filo Mix Wilman Kala 30 0 0
Filo Mix NULL 150 0 1
Ikura NULL 0 0 0
Ikura Romero y tomillo 20 0 0
Ikura Wilman Kala 50 0 0
Ikura NULL 70 0 1
Outback Lager Eastern Connection 10 0 0
Outback Lager Wilman Kala 30 0 0
Outback Lager NULL 40 0 1
NULL NULL 310 1 1
(15 row(s) affected)
Z. Use SELECT with GROUP BY, an aggregate function, and ROLLUP
This example uses a SELECT query that contains an aggregate function and a GROUP BY clause, which lists pub_name, au_lname, and title, in that order.
USE pubs
SELECT pub_name, au_lname, title, SUM(qty) AS 'SUM'
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titles.title_id = titleauthor.title_id INNER JOIN publishers
ON publishers.pub_id = titles.pub_id INNER JOIN sales
ON sales.title_id = titles.title_id
GROUP BY pub_name, au_lname, title
WITH ROLLUP
By using the ROLLUP operator, these groupings are created by moving right to left along the list of columns.
pub_name au_lname title SUM(qty)
pub_name au_lname NULL SUM(qty)
pub_name NULL NULL SUM(qty)
NULL NULL NULL SUM(qty)
NULL represents all values for that column.
If you use the SELECT statement without the ROLLUP operator, the statement creates a single grouping. The query returns a sum value for each unique combination of pub_name, au_lname, and title.
pub_name au_lname title SUM(qty)
Compare these examples with the groupings created by using the CUBE operator on the same query.
pub_name au_lname title SUM(qty)
pub_name au_lname NULL SUM(qty)
pub_name NULL NULL SUM(qty)
NULL NULL NULL SUM(qty)
NULL au_lname title SUM(qty)
NULL au_lname NULL SUM(qty)
pub_name NULL title SUM(qty)
NULL NULL title SUM(qty)
The groupings correspond to the information returned in the result set. NULL in the result set represents all values in the column. The ROLLUP operator returns the following data when the columns (pub_name, au_lname, title) are in the order listed in the GROUP BY clause:
- Quantity of each title that each publisher has sold for each author
- Quantity of all titles each publisher has sold for each author
- Quantity of all titles each publisher has sold
- Total quantity of all titles sold by all publishers for all authors
Here is the result set:
pub_name au_lname title SUM
----------------- ------------ ------------------------------------ ---
Algodata Infosys Bennet The Busy Executive's Database Guide 15
Algodata Infosys Bennet NULL 15
Algodata Infosys Carson NULL 30
Algodata Infosys Dull Secrets of Silicon Valley 50
Algodata Infosys Dull NULL 50
... ...
New Moon Books White Prolonged Data Deprivation: Four 15
New Moon Books White NULL 15
New Moon Books NULL NULL 316
NULL NULL NULL 791
(49 row(s) affected)
The GROUPING function can be used with the ROLLUP operator or with the CUBE operator. You can apply this function to one of the columns in the select list. The function returns either 1 or 0 depending upon whether the column is grouped by the ROLLUP operator.
a. Use the INDEX optimizer hint
This example shows two ways to use the INDEX optimizer hint. The first example shows how to force the optimizer to use a nonclustered index to retrieve rows from a table and the second example forces a table scan by using an index of 0.
-- Use the specifically named INDEX.
USE pubs
SELECT au_lname, au_fname, phone
FROM authors WITH (INDEX(aunmind))
WHERE au_lname = 'Smith'
Here is the result set:
au_lname au_fname phone
-------------------------------------- -------------------- ----------
Smith Meander 913 843-0462
(1 row(s) affected)
-- Force a table scan by using INDEX = 0.
USE pubs
SELECT emp_id, fname, lname, hire_date
FROM employee (index = 0)
WHERE hire_date > '10/1/1994'
b. Use OPTION and the GROUP hints
This example shows how the OPTION (GROUP) clause is used with a GROUP BY clause.
USE pubs
SELECT a.au_fname, a.au_lname, SUBSTRING(t.title, 1, 15)
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id
GROUP BY a.au_lname, a.au_fname, t.title
ORDER BY au_lname ASC, au_fname ASC
OPTION (HASH GROUP, FAST 10)
c. Use the UNION query hint
This example uses the MERGE UNION query hint.
USE pubs
SELECT *
FROM authors a1
OPTION (MERGE UNION)
SELECT *
FROM authors a2
d. Use a simple UNION
The result set in this example includes the contents of the ContactName, CompanyName, City, and Phone columns of both the Customers and SouthAmericanCustomers tables.
USE Northwind
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'SouthAmericanCustomers')
DROP TABLE SouthAmericanCustomers
GO
-- Create SouthAmericanCustomers table.
SELECT ContactName, CompanyName, City, Phone
INTO SouthAmericanCustomers
FROM Customers
WHERE Country IN ('USA', 'Canada')
GO
-- Here is the simple union.
USE Northwind
SELECT ContactName, CompanyName, City, Phone
FROM Customers
WHERE Country IN ('USA', 'Canada')
UNION
SELECT ContactName, CompanyName, City, Phone
FROM SouthAmericanCustomers
ORDER BY CompanyName, ContactName ASC
GO
e. Use SELECT INTO with UNION
In this example, the INTO clause in the first SELECT statement specifies that the table named CustomerResults holds the final result set of the union of the designated columns of the Customers and SouthAmericanCustomers tables.
USE Northwind
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CustomerResults')
DROP TABLE CustomerResults
GO
USE Northwind
SELECT ContactName, CompanyName, City, Phone INTO CustomerResults
FROM Customers
WHERE Country IN ('USA', 'Canada')
UNION
SELECT ContactName, CompanyName, City, Phone
FROM SouthAmericanCustomers
ORDER BY CompanyName, ContactName ASC
GO
f. Use UNION of two SELECT statements with ORDER BY
The order of certain parameters used with the UNION clause is important. This example shows the incorrect and correct use of UNION in two SELECT statements in which a column is to be renamed in the output.
/* INCORRECT */
USE Northwind
GO
SELECT City
FROM Customers
ORDER BY Cities
UNION
SELECT Cities = City
FROM SouthAmericanCustomers
GO
/* CORRECT */
USE Northwind
GO
SELECT Cities = City
FROM Customers
UNION
SELECT City
FROM SouthAmericanCustomers
ORDER BY Cities
GO
g. Use UNION of three SELECT statements showing the effects of ALL and parentheses
These examples use UNION to combine the results of three tables, in which all have the same 5 rows of data. The first example uses UNION ALL to show the duplicated records, and returns all 15 rows. The second example uses UNION without ALL to eliminate the duplicate rows from the combined results of the three SELECT statements, and returns 5 rows.
The final example uses ALL with the first UNION, and parentheses around the second UNION that is not using ALL. The second UNION is processed first because it is in parentheses, and returns 5 rows because the ALL option is not used and the duplicates are removed. These 5 rows are combined with the results of the first SELECT through the UNION ALL keywords, which does not remove the duplicates between the two sets of 5 rows. The final result has 10 rows.
USE Northwind
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CustomersOne')
DROP TABLE CustomersOne
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CustomersTwo')
DROP TABLE CustomersTwo
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CustomersThree')
DROP TABLE CustomersThree
GO
USE Northwind
GO
SELECT ContactName, CompanyName, City, Phone INTO CustomersOne
FROM Customers
WHERE Country = 'Mexico'
GO
SELECT ContactName, CompanyName, City, Phone INTO CustomersTwo
FROM Customers
WHERE Country = 'Mexico'
GO
SELECT ContactName, CompanyName, City, Phone INTO CustomersThree
FROM Customers
WHERE Country = 'Mexico'
GO
-- Union ALL
SELECT ContactName
FROM CustomersOne
UNION ALL
SELECT ContactName
FROM CustomersTwo
UNION ALL
SELECT ContactName
FROM CustomersThree
GO
USE Northwind
GO
SELECT ContactName
FROM CustomersOne
UNION
SELECT ContactName
FROM CustomersTwo
UNION
SELECT ContactName
FROM CustomersThree
GO
USE Northwind
GO
SELECT ContactName
FROM CustomersOne
UNION ALL
(
SELECT ContactName
FROM CustomersTwo
UNION
SELECT ContactName
FROM CustomersThree
)
GO