1 / 27

T-SQL Essentials: Chapter 12 Aggregation

T-SQL Essentials: Chapter 12 Aggregation. Syed Rizvi. Objectives. Aggregations Count / Count_Big Sum Max/Min Avg Grouping Data GROUP BY HAVING Distinct Values. Aggregations. Performing a function on a set of data to return one aggregated value per grouping of data.

badu
Download Presentation

T-SQL Essentials: Chapter 12 Aggregation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. T-SQL Essentials: Chapter 12Aggregation Syed Rizvi

  2. Objectives • Aggregations • Count / Count_Big • Sum • Max/Min • Avg • Grouping Data • GROUP BY • HAVING • Distinct Values

  3. Aggregations • Performing a function on a set of data to return one aggregated value per grouping of data. • This will vary from counting the number of rows returned from a SELECT statement to figuring out maximum and minimum values.

  4. AggregationsCOUNT/COUNT_BIG • COUNT/COUNT_BIG is probably the most commonly used aggregation • finds out the number of rows returned from a query. • You use this for checking the total number of rows in a table or, more likely, the number of rows returned from a particular set of filtering criteria. • Quite often this is used to cross-check the number of rows from a query in SSE with the number of rows an application is showing to a user. • The syntax is COUNT(*) or COUNT_BIG(*). There are no columns defined, because it is rows that are being counted. • The difference in these two functions is that COUNT returns an integer data type, and COUNT_BIGreturns a bigint data type

  5. AggregationsCOUNT/COUNT_BIGExample SELECT COUNT(*) AS ' Number of Rows‘ FROM ShareDetails. Shares

  6. AggregationsCOUNT/COUNT_BIGExample SELECT COUNT(*) AS ' Number of Rows‘ FROM ShareDetails. Shares WHERE CurrentPrice > 10

  7. AggregationsSUM • If you have numeric values in a column, it is possible to aggregate them as a summation. • The ideal scenario for this is to aggregate the number of transactions in a bank account to see how much the balance has changed by. This could be daily, weekly, monthly, or over any time period required. • A negative amount would show that more has been taken out of the account than put in, for example.

  8. AggregationsSUM • The syntax : SUM(column1| @variable| Mathematical function). • The summation does not have to be of a column but could include a math function. • One example would be to sum up the cost of purchasing shares, so you would multiply the number of shares bought by the cost paid.

  9. AggregationsSUM Example SELECT SUM(Amount) AS ' Amount Deposited‘ FROM TransactionDetails. Transactions WHERE CustomerId = 1 AND TransactionType = 1

  10. AggregationsSUM Example

  11. AggregationsMAX/MIN • Performed on a set of data to get the minimum and maximum values of a column of data. • This is useful if you want to see values such as the smallest share price or the greatest portfolio value • Or the maximum number of sales of each product in a period of time, or the minimum sold, so that you can see whether some days are quieter than others.

  12. AggregationsMAX/MIN Example SELECT MAX(Price) MaxPrice, MIN(Price) MinPrice FROM ShareDetails. Sh

  13. Grouping Data using Group ByExample • The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. • Groups the data to return and provide a summary value for each grouping of data. • The basic syntax for grouping: SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name • GROUP BY [ ALL] (column1[ , column2, . . . ]) • The option ALL is a bit like an OUTER JOIN. If you have a WHERE statement as part of your SELECT statement, any grouping filtered out will still return a row in the results, but instead of aggregating the column, a value of NULL will be returned. • You can use this as a checking mechanism. You can see the rows with values and the rows without values, and visually this will tell you that your filtering is correct. • Any column defined in the SELECT statement that does not form part of the aggregation must be contained within the GROUP BY clause and • be in the same order as the SELECT statement. • Failure to do this will mean that the query will give erroneous results and in many cases use a lot of resources in giving these results.

  14. Grouping Data using Group ByExample

  15. Grouping Data using Group ByExample Now we want to find the total sum (total order) of each customer. We will have to use the GROUP BY statement to group the customers. We use the following SQL statement: SELECT Customer, SUM(OrderPrice) FROM OrdersGROUP BY Customer

  16. Grouping Data using Group ByExample Problem: Now we want to find the total sum (total order) of each customer. We will have to use the GROUP BY statement to group the customers. We use the following SQL statement: SELECT Customer, SUM(OrderPrice) FROM OrdersGROUP BY Customer

  17. Grouping Data using Group ByExample

  18. Grouping Data using Group ByExample • find maximum and minimum values for every share that has a row in the ShareDetails. SharePrices table where the share ID is less than 9999. • The row that has no Share record will be excluded. SELECT ShareId, MIN(Price) MinPrice, Max(Price) MaxPrice FROM ShareDetails. SharePrices WHERE ShareId < 9999 GROUP BY ShareId

  19. Grouping Data using Group ByExample

  20. Having • Is used in conjunction with Group By • Behaves like a filter • Work on Aggregation of Data • Also include aggregation condition Syntax GROUP BY column1[, column2. . . ] HAVING [ aggregation_condition]

  21. HavingExample SELECT CustomerId, COUNT(*) FROM CustomerBankTransactions WHERE TransactionDate BETWEEN ' 1 Aug 2010 ' AND ' 31 Aug 2010 ‘ GROUP BY CustomerId HAVING COUNT(*) > 20

  22. HavingExample Retrieve Rows Where Min Share Price is greater than $10: SELECT sp. ShareId, s. ShareDesc, MIN(Price) MinPrice, Max(Price) Max FROM ShareDetails. SharePrices sp LEFT J OIN ShareDetails. Shares s ON s. ShareId = sp. ShareId WHERE sp. ShareId < 9999 GROUP BY ALL sp. ShareId, s. ShareDesc HAVING MIN(Price) > 10

  23. Distinct Values • Some tables might have multiple entries having same values • You might want to filter only one row • Place the keyword DISTINCT after the SELECT statement and before the list of columns.

  24. Distinct ValuesExample This query may return multiple rows for a share price SELECT s. ShareDesc, sp. Price, sp. PriceDate FROM ShareDetails. Shares s JOIN ShareDetails. SharePrices sp ON sp. ShareId = s. ShareId

  25. Distinct ValuesExample Placing DISTINCT at the start of the column list doesn’t make any difference, because there are different prices and different price dates. SELECT DISTINCT s. ShareDesc, sp. Price, sp. PriceDate FROM ShareDetails. Shares s JOIN ShareDetails. SharePrices sp ON sp. ShareId = s. ShareId

  26. Distinct ValuesExample What about this: SELECT DISTINCT s. ShareDesc FROM ShareDetails. Shares s JOIN ShareDetails. SharePrices sp sp. ShareId = s. ShareId

More Related