Advanced Query Concepts - Sql or Pl-Sql

Advanced Query Concepts
After you have mastered query fundamentals, you can explore these advanced query concepts for query solutions:
· Using aggregate functions in the select list
· Grouping rows with GROUP BY
· Combining results with UNION
· Subquery fundamentals
· Conditional data processing using CASE
· Parallel queries
· Summarizing data


Using COUNT(*)
COUNT(*) does not require an expression parameter because it does not use information about any particular column. It counts the total number of rows that meet the qualifications of the query. COUNT(*) returns the number of rows that match the search conditions specified in the query without eliminating duplicates. It counts each row separately, including rows that contain null values. This query finds the total number of books in titles:USE pubsSELECT COUNT(*)FROM titles
Here is the result set:
18
(1 row(s) affected)

COUNT(*) can be combined with other aggregate functions. This query shows COUNT(*) combined with an AVG function in which both aggregate functions aggregate data only from the rows that satisfy the WHERE clause search condition:

USE pubs
SELECT COUNT(*), AVG(price)
FROM titles
WHERE advance > $1000

Here is the result set:
----------- ------
15 14.42
(1 row(s) affected)


Using DISTINCT
The DISTINCT keyword is optional with SUM, AVG, and COUNT. When DISTINCT is used, duplicate values are eliminated before the sum, average, or count is calculated.
If you use DISTINCT, the expression must consist of a column name only. It cannot include an arithmetic expression.
This query returns the average prices of business books (without duplicate values):
USE pubs
SELECT AVG(DISTINCT price)FROM titlesWHERE type = ‘business’

Here is the result set:
14.64
(1 row(s) affected)

Without DISTINCT, the AVG function finds the average price of all business titles:
USE pubs
SELECT AVG(price)FROM titlesWHERE type = ‘business


Here is the result set:
13.73
(1 row(s) affected)

Null Values
Null values in a column are ignored while an aggregate function is operating. For example, the count of advances in the titles table is not the same as the count of title names because null values in the advance column are not counted.
USE pubs
SELECT COUNT(advance)
FROM titles
Here is the result set:
16
(1 row(s) affected)

USE pubs
SELECT COUNT(title)FROM titles
Here is the result set:
18
(1 row(s) affected)

If no rows meet the condition(s) specified in the WHERE clause, COUNT returns a value of zero. The other functions all return NULL. COUNT(*), counts each row, even if all column values are NULL. Here are examples:
USE pubs
SELECT COUNT(DISTINCT title)FROM titlesWHERE type = ‘poetry’
Here is the result set:
0
(1 row(s) affected)

USE pubs
SELECT AVG(advance)FROM titlesWHERE type = ‘poetry’
Here is the result set:
(null)
(1 row(s) affected)


Grouping Rows with GROUP BY
The GROUP BY clause is used to produce aggregate values for each row in the result set. When used without a GROUP BY clause, aggregate functions report only one aggregate value for a SELECT statement.
This example returns the number of units sold for each product in category 2:
USE Northwind
SELECT OrdD.ProductID AS ProdID,SUM(OrdD.Quantity) AS AmountSold
FROM [Order Details] AS OrdD
JOIN Products as PrdON OrdD.ProductID = Prd.ProductID AND Prd.CategoryID = 2
GROUP BY OrdD.ProductID

The GROUP BY keywords are followed by a list of columns, known as the grouping columns. The GROUP BY clause restricts the rows of the result set; there is only one row for each distinct
value in the grouping column or columns. Each result set row contains summary data related to the specific value in its grouping columns.
There are restrictions on the items that can be specified in the select list when a SELECT statement contains a GROUP BY. Items allowed in the select list are:
· The grouping columns.
· Expressions that return only one value for each value in the grouping columns, such as aggregate functions that have a column name as one of their parameters. These are known as vector aggregates.

You cannot use GROUP BY or HAVING on ntext,text,image, or bit columns unless they are in a function that returns a value having another data type.
Examples of such functions are SUBSTRING and CAST.