| With version 7.0, Microsoft added more than 30 functions to an already large number of functions. More were added in SQL Server 2000 as well to support new features or capabilities, or to provide additional functionality. Some of the SQL Server functions provide shortcuts to obtain information that could be retrieved in other ways. For instance, one function will obtain an object ID ( OBJECT_ID() ) if you know the object's name , but looking it up in the sysobjects table could also work. Other functions, such as some of the mathematical functions, are more essential. (Okay, it could be argued that you can calculate the square root, for instance, using T-SQL code, but it is not efficient.) Most functions have the following structure: FUNCTION_NAME([ parameter1 [, parameter2 [, ...]]) The parameters might be an expression (such as a column name or another function), a constant, or a special code (such as a formatting code). A function returns a value. The datatype for the value depends on the function you are using. Take a look at the available functions grouped by category. String Functions The string functions allow you to perform concatenation, parsing manipulation, and so on with strings. TIP Excessive use of string functions against a column might indicate that the column should be split into several columns. For example, if you find yourself frequently parsing out first name and last name from a name column, perhaps you should split the name into two columns . | Table 26.3 lists the available string functions. They can be used against any string expression. Table 26.3. String Functions | Function Name | Returns | | ASCII( char ) | The ASCII code for the leftmost character in char . | | CHAR( int ) | The ASCII character represented by int (an ASCII code). | | CHARINDEX( char_pattern , char , [ int_start ]) | Starting location of char_pattern within char , optionally starting search at int_start . | | DIFFERENCE( char1 , char2 ) | The difference between the two character expressions. Used for a phonetic match. | | LEFT( char , int ) | int characters from left of char . | | LEN( char ) | Number of characters in char , excluding trailing blanks. | | LOWER( char ) | char in lowercase. | | LTRIM( char ) | char without leading spaces. | | NCHAR( int ) | The character for a given Unicode value. | | PATINDEX( char_pattern , char ) | Starting position of char_pattern in char , or if the pattern is not found. | | REPLACE( char1 , char2 , char3 ) | String with all occurrences of char2 replaced with char3 in char1 . | | QUOTENAME( char , [ char_quote ]) | char as a valid quoted identifier. Adds the characters [ and ] at the beginning and end of char (this default can be changed to ' or " , specified as char_quote ). Returns a Unicode string. | | REPLICATE( char , int ) | Repeat of char expression int number of times. | | REVERSE( char ) | Reverse of char . | | RIGHT( char , int ) | int characters from right of char . | | RTRIM( char ) | char without trailing spaces. | | SOUNDEX( char ) | A four-character string used for comparison of a phonetic match. | | SPACE( int ) | A string of int spaces. | | STR( float , [ length , [ decimal ]]) | float as a character string, with length of length and decimal numbers of decimals. Default length is 10 and default number of decimals is 0. | | STUFF( char1 , start , length , char2 ) | A string with length number of characters from char1 replaced with char2 , starting at start . | | SUBSTRING( char , start , length ) | A string of length number of characters from char , from start position. | | UNICODE( char ) | The Unicode code for the leftmost character in char . | | UPPER( char ) | char in uppercase. | You can use the operator + to concatenate strings. The following example uses SUBSTRING and string concatenation to present each author's first letter of the first name and then the last name: SELECT SUBSTRING(au_fname,1,1) + '. ' + au_lname FROM authors Mathematical Functions The mathematical functions in Table 26.4 perform calculations based on the input values and return a numeric value. No new mathematical functions were introduced in version 7.0. Table 26.4. Mathematical Functions | Function Name | Returns | | ABS(numeric) | The absolute (positive) value of numeric. | | ACOS( float ) | The arc cosine for float . | | ASIN(float) | The arc sine for float . | | ATAN( float ) | The arc tangent for float . | | ATAN2( float1 , float2 ) | The arc tangent whose tangent is between float1 and float2 . | | CEILING( numeric ) | The smallest integer value that is higher than or equal to numeric . | | COS( float ) | The trigonometric cosine of float . | | COT( float ) | The trigonometric cotangent of float . | | DEGREES( numeric ) | The number of degrees for a given angle, numeric , given in radians. | | EXP( float ) | The exponential value of float . | | FLOOR( numeric ) | The largest integer value that is lower than or equal to numeric . | | LOG( float ) | The natural logarithm of float . | | LOG10(float) | The base-10 logarithm of float . | | PI() | The constant pi. | | POWER( numeric1 , numeric2 ) | The value of numeric1 to the specified power, given in numeric2 . | | RADIANS( numeric ) | Radians of numeric , given in degrees. | | RAND([ seed ]) | A random value between 0 and 1. seed can be specified as the starting value. | | ROUND( numeric , length , func ) | Number rounded to the specified numeric to specified length . If func is specified and not 0, numeric is rounded down to length . | | SIGN( numeric ) | 1 if numeric is positive, 0 if numeric is 0, and “1 if numeric is negative. | | SIN( float ) | The trigonometric sine of float . | | SQUARE( float ) | The square of float . | | SQRT( float ) | The square root of float . | | TAN( float ) | The trigonometric tangent of float . | The operators + , - , * , / , and % (modulo) are also available for numeric expressions. Date Functions The date functions perform operations such as formatting and subtracting. The expression given is a datetime datatype. Some of the functions take a datepart as argument. The datepart specifies on what part of the datetime datatype you want to operate . Table 26.5 provides the codes for the datepart. Table 26.5. Available Codes for Datepart | Datepart | Abbreviation | Possible Values | | year | yy | 1753 “9999 | | quarter | qq | 1 “4 | | month | mm | 1 “12 | | day of year | dy | 1 “366 | | day | dd | 1 “31 | | week | wk | 1 “53 | | weekday | dw | 1 “7 | | hour | hh | 0 “23 | | minute | mi | 0 “59 | | second | ss | 0 “59 | | millisecond | ms | 0 “999 | The date- and time- related functions are listed in Table 26.6. Table 26.6. Date-and Time-Related Functions | Function Name | Returns | New in SQL Server 2000 | | DATEADD( datepart , int , date ) | Date expression as a result of adding int datepart s to date . | | | DATEDIFF( datepart , date1 , date2 ) | The number of datepart s between date1 and date2 . | | | DATENAME( datepart , date ) | The datepart of date returned as a character string. For month and day of week, the actual name is returned. | | | DATEPART( datepart , date ) | The datepart of date returned as an integer. | | | DAY( date ) | The day-of-month part as an integer. | | | GETDATE() | The current date and time. | | | GETUTCDATE() | The current date and time as Universal Time Coordinate Time (Greenwich Mean Time). | Yes | | MONTH( date ) | The month as an integer. | | | YEAR( date ) | The year as an integer. | | You can use the operators + and - directly on datetime expressions in version 7.0 and later. The implied datepart is days. In this example, you use the + operator to add one day to the current date: SELECT GETDATE(), GETDATE() + 1 go ----------------------- ---------------------- 1998-03-28 16:08:33 1998-03-29 16:08:33 Metadata Functions The metadata functions, listed in Table 26.7, are useful for retrieving information such as column names, table names , index keys, and so on. Basically, many of the functions are shortcuts for querying the system tables. TIP It is better to use the system functions than to directly query the system tables. If the system tables change in forthcoming releases of SQL Server (as they did with version 7.0), your applications and scripts will still work if you use the system functions. You can also use the information schema views for retrieving system-table “related information. The views are ANSI standard and are independent of the system tables, and all have the object owner INFORMATION_SCHEMA . | Table 26.7. Metadata Functions | Function Name | Returns | New in SQL Server 2000 | | COL_LENGTH( table , column ) | The length of column in table . | | | COL_NAME( table_id , column_id ) | The name of column_id in table_id . | | | COLUMNPROPERTY( id , column , property ) | Information about a column in a table, given the table id . Returns information for a parameter, given in column , for a stored procedure. The property parameter defines the type of information to be returned. | | | DATABASEPROPERTY ( database_name , property ) | Setting of specified property for database_name . Included for backward compatibility, use DATABASEPROPERTYEX instead. | | | DATABASEPROPERTYEX ( database_name , property ) | Current setting of specified property for database_name as a sql_variant. | Yes | | DB_ID([ db_name ]) | The database ID of db_name or the current database. | | | DB_NAME([ db_id ]) | The database name of db_id or the name of the current database. | | | FILE_ID( filename ) | The ID for filename . | | | FILE_NAME( file_id ) | The filename for file_id . | | | FILEGROUP_ID( filegroupname ) | The ID for filegroupname . | | | FILEGROUP_NAME( filegroup_id ) | The filegroup name for filegroup_id . | | | FILEGROUPPROPERTY ( filegroup_name,property ) | The value of property for filegroup_name . | | | FILEPROPERTY ( filename, property ) | The value of property for filename . | | | FULLTEXTCATALOGPROPERTY ( catalog_name, property ) | The value of property for full-text catalog catalog_name . | | | FULLTEXTSERVICEPROPERTY ( property ) | Information about property for full-text service-level. | | | INDEX_COL( table , index_id , key_id ) | The column name for the specified table , index_id , and key_id . | | | INDEXKEY_PROPERTY ( table_ID , index_ID , key_ID , property ) | The property information about index key key_ID for index_ID on table_ID . | Yes | | INDEXPROPERTY (table_ID , index , property ) | The property info for index index on table_ID . | | | OBJECT_ID( object_name ) | The ID for object_name . | | | OBJECT_NAME( object_id ) | The database object name for object_id . | | | OBJECTPROPERTY ( object_id , property ) | Information for object_id . property defines the type of information to be returned. | | | SQL_VARIANT_PROPERTY ( expression,property ) | The property information about sql_variant expression, such as base datatype, as a sql_variant. | Yes | | TYPEPROPERTY( datatype , property ) | Information defined in property for datatype . | | System Functions The system functions, listed in Table 26.8, are useful for retrieving information about values, options, and settings within the SQL Server. Table 26.8. System Functions | Function Name | Returns | New in SQL Server 2000 | | APP_NAME() | The name of the application that executes the function. | | | CAST( expression AS datatype ) | The CAST function is a synonym for the CONVERT function and converts expression to datatype . | | | COALESCE( expr1 , [ expr2 ,,,]) | The first non-null expression in the list. | | | COLLATIONPROPERTY ( collation_name , property ) | The specified property of the collation_name. property can be CodePAgeLCID , ComparisonStyle . | Yes | | CONVERT( datatype[(length)] , expression , style ) | Converts expression to datatype . For conversion of datetime or float expressions, style defines the formatting. | | | DATALENGTH( expression ) | The storage area of expression , including trailing blanks for character information. | | | GETANSINULL([ db_name ]) | The default nullability option of db_name for the current database. | | | GETCHECKSUM( col_name ) | A checksum value for the values in col_name . | | | HOST_ID() | The process ID of the client application's process. | | | HOST_NAME() | The client's workstation name. | | | IDENT_CURRENT( 'tablename ') | The last identity value generated for tablename by any session and within any scope. | Yes | | IDENT_INCR( table ) | The identity increment for the identity column in table . | | | IDENT_SEED( table ) | The identity seed for the identity column in table . | | | IDENTITY( datatype [, seed , increment ]) AS column_name | Used only in SELECT INTO to create identity column in new table. | | | ISDATE( char ) | 1 if char is in a valid datetime format; otherwise , 0. | | | ISNULL( expression , value ) | value if expression is NULL . | | | ISNUMERIC( char ) | 1 if char can be converted to a numeric value; otherwise, 0. | | | NEWID() | A generated global unique identifier. | | | NULLIF( expr1 , expr2 ) | Null if expr1 equals expr2 . | | | PARSENAME( object_name , object_part ) | Name of object_part (specified as an int) of object_name . | | | PERMISSIONS( object_id [, column ]) | A bitmap indicating permissions on object_id and optionally column . | | | ROWCOUNT_BIG() | Number of rows affected by previous statement executed in session as a bigint . | Yes | | SCOPE_IDENTITY() | The last identity value inserted into an IDENTITY column within current scope (for example, stored procedure, trigger, function, or batch). | Yes | | SERVER_PROPERTY(' property ') | The property information about the server as a sql_variant. | Yes | | SESSIONPROPERTY( option ) | The SET options of a session as a sql_variant. | Yes | | STATS_DATE( table_id , index_id ) | Date when the distribution page was updated for index_id on table_id . | | | TRIGGER_NESTLEVEL ([ tr_object_id ]) | Nesting level of specified or current trigger. | | The following example returns the title ID and price for all books. If the price is not set ( NULL ), it returns a price of 0: SELECT title_id, ISNULL(price, 0) FROM titles Let us expand the example. You want to display the string 'Not Priced' for those that contain NULL values. You have to convert the price to a character value before replacing NULL with your text string: SELECT title_id, ISNULL(CONVERT(CHAR(10),price), 'Not Priced') FROM titles Security Functions The security functions, listed in Table 26.9, are useful for retrieving information about users and roles. Table 26.9. Security Functions | Function Name | Returns | New in SQL Server 2000 | | HAS_DBACCESS( database ) | Information about whether current user has access to database. | Yes | | IS_MEMBER( group role ] | 1 if the user is a member of specified NT group or SQL Server role ; otherwise, 0. | | | IS_SRVROLEMEMBER ( role [, login ]) | 1 if the user's login ID is a member of the specified server role ; otherwise, 0. An explicit login name can be specified. | | | SUSER_ID( login_name ) | The loginid of the specified login_name . Included for backward compatibility; use SUSER_SID instead. | | | SUSER_NAME([ login_id ]) | The login name of login_id . Included for backward compatibility; use SUSER_SNAME instead. | | | SUSER_SID([ login ]) | Security identification number (SID) for login . | | | SUSER_SNAME([ login_id ]) | The login name of login_id . | | | USER_ID([ username ]) | The user ID for username . | | | USER_NAME([ user_id ]) | The username for current user or user_id . | | The following example returns the current user's database username, login name, and whether the user is a member of the db_owner role: select substring (user_name(), 1, 20) AS 'user_name', substring (suser_sname(),1, 30) AS 'login_name', case IS_MEMBER('db_owner') when 1 then 'Yes' else 'No' end AS 'Is_dbOwner' go user_name login_name Is_dbOwner -------------------- ------------------------------ ---------- dbo RRANKINSA20P\rrankins Yes Text and Image Functions These scalar functions, listed in Table 26.10, perform an operation on a text or image input value or column and return information about the value. Table 26.10. Text and Image Functions | Function Name | Returns | | TEXTPTR ( column_name ) | The text-pointer for a text or image column as a varbinary(16) value. Pointer can be used in READTEXT , WRITETEXT , and UPDATETEXT statements. | | TEXTVALID( ' table . column ', ptr ) | 1 if text or image pointer ptr is valid for table.column , 0 if pointer is invalid. | The following example shows how to use the TEXTPTR function to retrieve a text pointer and use it in the READTEXT command: USE pubs GO DECLARE @ptr varbinary(16) SELECT @ptr = TEXTPTR(pr_info) FROM pub_info WHERE pub_id = '0877' READTEXT pub_info.pr_info @ptr 0 65 GO pr_info ----------------------------------------------------------------- This is sample text data for Binnet & Hardley, publisher 0877 in Rowset Functions The rowset functions listed in Table 26.11 return an object that can be used in place of a table reference in a Transact-SQL statement. Table 26.11. Rowset Functions | Function Name | Returns | New in SQL Server 2000 | | CONTAINSTABLE ( table , column * } , ' contains_conditions ' [, top_n_by_rank ]) | A table of 0 or more rows for columns containing string data using precise or fuzzy matches to a single word or phrases, the proximity of words to one another, or weighted matches, as specified in contains_conditions . Result can be limited to the top n matching rows ordered by rank. | | | FREETEXTTABLE ( table , { column * } , ' freetext_ string ' [ , top_n_by_rank ] ) | A table of 0 or more rows that match the meaning of the text in freetext_string . table is a table marked for full-text querying. column must be columns that contain string data. Result can be limited to the top n matching rows ordered by rank. | | | OPENDATASOURCE ( provider_name , init_string ) | The connection information used as the first part ( servername ) of a four-part fully qualified object name. Can be used in place of a linked server name. Should only reference OLE DB data sources. | Yes | | OPENQUERY ( linked_server , 'query' ) | Resultset from specified pass-through query on linked_server . | | | OPENROWSET ( provider_name , {' datasource ';' user_id '; ' password '' provider_string '} , {[ catalog .][ schema. ] object ' query '} ) | Remote data result from specified connection to OLE DB data source. | | | OPENXML( docid , rowpattern , flag ) [WITH ( schemaDeclaration tablename )] | Resultset from an XML document specified with docid . Data is returned in edge table format unless SchemaDeclaration or TableName is specified. | Yes | The following example uses the OPENROWSET function and the Microsoft OLE DB Provider for SQL Server to titles records from the titles table in the pubs database on a remote server named RRANKINSA20P . Notice how the result from the function can be used just like a table, even in a JOIN clause: select p.pub_id, t.title from publishers p join OPENROWSET('SQLOLEDB','RRANKINSA20P';'sa';'', 'SELECT * FROM pubs.dbo.titles ORDER BY pub_id') AS t on p.pub_id = t.pub_id go pub_id title ------ ------------------------------------------------------------------------ 0736 You Can Combat Computer Stress! 0736 Is Anger the Enemy? 0736 Life Without Fear 0736 Prolonged Data Deprivation: Four Case Studies 0736 Emotional Security: A New Algorithm 0877 Silicon Valley Gastronomic Treats 0877 The Gourmet Microwave 0877 The Psychology of Computer Cooking 0877 Computer Phobic AND Non-Phobic Individuals: Behavior Variations 0877 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 0877 Fifty Years in Buckingham Palace Kitchens 0877 Sushi, Anyone? 1389 The Busy Executive's Database Guide 1389 Cooking with Computers: Surreptitious Balance Sheets 1389 Straight Talk About Computers 1389 But Is It User Friendly? 1389 Secrets of Silicon Valley 1389 Net Etiquette To learn more about the CONTAINSTABLE and FREETEXTTABLE functions, see Chapter 44, "SQL Server Full-Text Search Services." For more information on using XML documents and the OPENXML function, see Chapter 41, "Using XML in SQL Server 2000." Niladic Functions The niladic group of functions, listed in Table 26.12, is basically a set of system functions. The reason for grouping them separately is that they are used without parentheses after the function name. They are defined in the ANSI SQL-92 standard. You often find niladic functions used as defaults in CREATE TABLE and ALTER TABLE . NOTE Niladic functions are basically aliases to SQL Server system functions. If you use them for default values in tables and run sp_help for the table, edit the table, or script the table in Enterprise Manager, you will notice that they are translated to the corresponding system function. | Table 26.12. Niladic Functions | Function Name | Returns | Corresponding System Function | | CURRENT_TIMESTAMP | Current date and time | GETDATE() | | CURRENT_USER | The user's username | USER_NAME() | | SESSION_USER | The user's username | USER_NAME() | | SYSTEM_USER | The user's login name | SUSER_NAME() | | USER | The user's username | USER_NAME() | In Listing 26.10, you create a table with three columns with defaults for the current datetime, the user's login name, and the username. The INSERT statement inserts default values for all columns, and the SELECT statements retrieve the row inserted. Listing 26.10 Using Niladic Functions with the INSERT Statement CREATE TABLE my_defaults (the_datetime DATETIME DEFAULT CURRENT_TIMESTAMP, users_login CHAR(128) DEFAULT SYSTEM_USER, users_name CHAR(128) DEFAULT CURRENT_USER,) INSERT my_defaults DEFAULT VALUES SELECT * FROM my_defaults go the_datetime users_login users_name --------------------------- -------------------- ------------------ 1998-03-29 19:09:52.377 sa dbo Aggregate Functions The aggregate functions differ from those in the other groups. Aggregate functions perform an aggregation for a column over a set of rows. Table 26.13 lists the aggregate functions available in SQL Server. Table 26.13. Aggregate Functions | Function Name | Returns | New in SQL Server 2000 | | AVG([ALL DISTINCT] expression ) | The average of all values given in expression . | | | BINARY_CHECKSUM (* expression [ , ... n ] | The binary checksum computed over a row or the list of expressions. Can be used to detect changes to a row. | Yes | | CHECKSUM(* expression [ , ... n ] | The checksum value computed over a row or the list of expressions. Intended for use in building hash indices. | Yes | | CHECKSUM_AGG [ALL DISTINCT] expression ) | The checksum of the values in a group expression, ignoring nulls. | Yes | | COUNT([ALL DISTINCT] expression *) | The number of non- NULL values in expression . NULL s are counted if * is specified. | | | COUNT_BIG([ALL DISTINCT] expression *) | The number of non- NULL values in expression as a bigint. NULL s are counted if * is specified. | Yes | | GROUPING ( column_name ) | 1 when a row is added to GROUP BY resultset by CUBE or ROLLUP operator. 0 when not. | | | MAX([ALL DISTINCT] expression ) | The maximum value in expression . | | | VARP( expression ) | The statistical variance for the population for all values in the given expression . | | | STDEVP( expression ) | The statistical standard deviation for the population for all values in the given expression . | | | MIN([ALL DISTINCT] expression ) | The minimum value in expression . | | | SUM([ALL DISTINCT] expression ) | The sum of all values in expression . | | | VAR( expression ) | The statistical variance of all values in the given expression . | | | STDEV( expression ) | The statistical standard deviation of all values in the given expression . | | If you add the keyword DISTINCT , only distinct values will be aggregated. The default is ALL . You should note that NULL values are not included in the aggregates, except for COUNT(*) , which counts the number of rows returned from the relational expression. Perhaps you want to count the number of rows, prices, and distinct prices in the title table: SELECT COUNT(*) AS Total, COUNT(price) AS Prices, COUNT(DISTINCT price) AS "Distinct prices" FROM titles go Total Prices Distinct prices ----------- ----------- --------------- 18 16 11 Apparently, two books are not priced yet, or the price is not known ( NULL ), and there are a total of five duplicate prices. Now, you want to perform some real aggregation over the prices: SELECT MAX(price) AS 'Max', MIN(price) AS 'Min', AVG(price) AS 'Average' FROM titles go Max Min Average --------------------- --------------------- --------------------- 22.9500 2.9900 14.7662 Warning: Null value is eliminated by an aggregate or other SET operation. Note that even though NULL usually sorts low, the minimum price is 2.99 because NULL is excluded from the aggregate. (The warning message displayed indicates that NULL values were encountered and excluded from the aggregate result.) The average price also does not include the nulls and is calculated only on the 16 rows that contain NULL . To include the rows containing NULL values, use the ISNULL() function to substitute a value for NULL and notice the difference in the average price: SELECT MAX(price) AS 'Max', MIN(price) AS 'Min', AVG(isnull(price, SELECT MAX(price) AS 'Max', MIN(price) AS 'Min', AVG(isnull(price, $0)) AS 'Average' FROM titles go Max Min Average --------------------- --------------------- --------------------- 22.9500 2.9900 13.1255 Warning: Null value is eliminated by an aggregate or other SET operation. )) AS 'Average' FROM titles go Max Min Average --------------------- --------------------- --------------------- 22.9500 2.9900 13.1255 Warning: Null value is eliminated by an aggregate or other SET operation. Aggregate functions are often used in conjunction with GROUP BY . The following example retrieves the average price for each book category: SELECT type, AVG(price) AS Average FROM titles GROUP BY type go type Average ------------ ---------- business 13.7300 mod_cook 11.4900 popular_comp 21.4750 psychology 13.5040 trad_cook 15.9633 UNDECIDED NULL The following example shows the use of CHECKSUM_AGG to detect changes in the publishers table: select checksum_agg(binary_checksum(*)) as old_checksum from publishers update publishers set state = 'MA' where state = 'CA' select checksum_agg(binary_checksum(*)) as new_checksum from publishers go old_checksum ------------ -1096815739 new_checksum ------------ -1096873083 Using COMPUTE and COMPUTE BY The COMPUTE and COMPUTE BY keywords enable you to return both detail and summary information in a single SELECT statement. The compute clause reports overall aggregate values for a resultset, as shown in the following example: /*list titles and prices, show overall max price*/ select title, price from titles compute max(price) go title price ---------------------------------------------------------------- ------------- The Busy Executive's Database Guide 19.9900 Cooking with Computers: Surreptitious Balance Sheets 11.9500 You Can Combat Computer Stress! 2.9900 Straight Talk About Computers 19.9900 Silicon Valley Gastronomic Treats 19.9900 The Gourmet Microwave 2.9900 The Psychology of Computer Cooking NULL But Is It User Friendly? 22.9500 Secrets of Silicon Valley 20.0000 Net Etiquette NULL Computer Phobic AND Non-Phobic Individuals: Behavior Variations 21.5900 Is Anger the Enemy? 10.9500 Life Without Fear 7.0000 Prolonged Data Deprivation: Four Case Studies 19.9900 Emotional Security: A New Algorithm 7.9900 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 20.9500 Fifty Years in Buckingham Palace Kitchens 11.9500 Sushi, Anyone? 14.9900 max ============= 22.9500 NOTE The way the computed aggregate is displayed is dependent on the query tool you are using. Query Analyzer will line up the computed aggregate under the column that is being aggregated, with a header indicating the type of aggregate. Not all query tools format the result from the COMPUTE clause as nicely . | The COMPUTE BY clause can be used in conjunction with an ORDER BY displaying subtotals within a resultset. You can also include the COMPUTE clause to display a grand total as well, as shown in the following example: /* display type, title and price, ** and show the maximum price for each type */ select type, title, price from titles order by type compute max(price) by type compute max(price) go type title price ------------ ------------------------------------------------------ ---------- business The Busy Executive's Database Guide 19.9900 business Cooking with Computers: Surreptitious Balance Sheets 11.9500 business You Can Combat Computer Stress! 2.9900 business Straight Talk About Computers 19.9900 max ========== 19.9900 type title price ------------ ------------------------------------------------------ ---------- mod_cook Silicon Valley Gastronomic Treats 19.9900 mod_cook The Gourmet Microwave 2.9900 max ========== 19.9900 type title price ------------ ------------------------------------------------------ ---------- popular_comp But Is It User Friendly? 22.9500 popular_comp Secrets of Silicon Valley 20.0000 popular_comp Net Etiquette NULL max ========== 22.9500 type title price ------------ ------------------------------------------------------ ---------- psychology Computer Phobic AND Non-Phobic Individuals 21.5900 psychology Is Anger the Enemy? 10.9500 psychology Life Without Fear 7.0000 psychology Prolonged Data Deprivation: Four Case Studies 19.9900 psychology Emotional Security: A New Algorithm 7.9900 max ========== 21.5900 type title price ------------ ------------------------------------------------------ ---------- trad_cook Onions, Leeks, and Garlic 20.9500 trad_cook Fifty Years in Buckingham Palace Kitchens 11.9500 trad_cook Sushi, Anyone? 14.9900 max ========== 20.9500 type title price ------------ ------------------------------------------------------ ----------- UNDECIDED The Psychology of Computer Cooking NULL max ========== max ========== 22.9500 Important point: compute by columns must match, in order, the columns specified in the order by clause. You can specify only a subset of the order by columns. Consider a query containing this order by clause: order by a, b, c The only allowed compute by clauses are the following: -
compute by a, b, c -
compute by a, b -
compute by a Expressions in a compute or compute by clause must match exactly the corresponding expression in the select list: select type, price, price*2 as 'double price' from titles where type = 'business' compute sum(price), sum(price*2) go type price double price ------------ --------------------- --------------------- business 19.9900 39.9800 business 11.9500 23.9000 business 2.9900 5.9800 business 19.9900 39.9800 sum ===================== 54.9200 sum ===================== 109.8400 NOTE Many organizations do not use compute for a variety of reasons. First, because it is not ANSI-standard SQL, organizations avoid writing important queries that might need to be modified dramatically to be ported to other database environments. Second, any decent reporting tool will provide all of the totaling and subtotaling directly within the report definition, with much more flexibility about the actual handling of the computed values. Third, not all database APIs or middle- tier database solutions can handle compute results properly. If you plan to use compute , make sure you have spent the necessary time testing how your application reads and presents computed values. |  |
