web 2.0

Compute and Compute BY clause

In the previous article I had explained about "order by" clause, now I will explain about the compute and compute by clause.COMPUTE and COMPUTE BY clause is used to generate new rows of data containing detailed and summary. COMPUTE  clause produces detail lines and a line that contains the summary. For example we will display all the data from Titles table from Pubs database, this is the same as ordinary SELECT command. After the last line of the data, we want to display the number of fields ytd_sales, for that we need the COMPUTE clause.

Here is the Example:
SELECT * FROM Titles compute sum(ytd_sales)
COMPUTE BY clause  producing new lines of summary data, similar to the GROUP BY clause, but the result rows as a subgroup with the values summarized. If you use COMPUTE BY clause must be accompanied with ORDER BY. For example we want to display data from table Titles sorted by Type, and after  Type, a total of ytd_sales displayed for each Type, then the command is:

SELECT type,ytd_sales FROM Titles ORDER BY type compute sum(ytd_sales) by type


Anonymous said...

how to get more than one values using sub query

i used the following query

select rno from tblroom where rno !=(select * from tblbook where bdate=date)

but i didn't get the required output instead it displays records with redundancy

Post a Comment