GROUP BY SalesOrderID, CarrierTrackingNumber SELECT SalesOrderID, CarrierTrackingNumber This query uses the LIKE clause in the HAVING clause. The second example shows a HAVING clause without aggregate functions. It groups the rows in the SalesOrderDetail table by product ID and eliminates products whose average order quantities are five or less. The first example that follows shows a HAVING clause with an aggregate function. The following example finds the average price of each type of product and orders the results by average price. NonDiscountSales = (OrderQty * UnitPrice) You can group by an expression if the expression does not include aggregate functions. The following example groups by an expression. SELECT ProductModelID, AVG(ListPrice) AS The following example puts the results into groups after retrieving only the rows with list prices greater than $1000. SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS , The following example finds the average price and the sum of year-to-date sales, grouped by product ID and special offer ID.
SELECT SalesOrderID, SUM(LineTotal) AS SubTotalīecause of the GROUP BY clause, only one row containing the sum of all sales is returned for each sales order.
The following example finds the total of each sales order in the database. ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN SELECT DISTINCT pp.LastName, pp.FirstNameįROM Person.Person pp JOIN HumanResources.Employee e This example uses two correlated subqueries to find the names of employees who have sold a particular product. WHERE p1.ProductModelID = p2.ProductModelID) This example returns only the rows for Product that have a product line of R and that have days to manufacture that is less than 4. SELECT Name, ProductNumber, ListPrice AS Price This example returns all rows (no WHERE clause is specified), and only a subset of the columns ( Name, ProductNumber, ListPrice) from the Product table in the AdventureWorks2012 database.
#Adventureworks2012 personttype code
This first code example returns all rows (no WHERE clause is specified) and all columns (using the *) from the Product table in the AdventureWorks2012 database. The following example shows three code examples. Using SELECT to retrieve rows and columns This topic provides examples of using the SELECT statement. Applies to: SQL Server (all supported versions) Azure SQL Database
0 kommentar(er)
