Compute By Clause in MSSQL server:

‘Compute by’ clause can be used with query to create a summary of result set, we demonstrate it using a real life example.
Here we create a Tables 1st one ‘Classification’ which contains the genera of music.
CREATE TABLE Classification (
Classif_ID integer NOT NULL PRIMARY KEY,
Classification varchar(25))

INSERT into Classification VALUES( 1,'Pop')
INSERT into Classification VALUES( 2,'Country')
INSERT into Classification VALUES( 3,'Alternative')
INSERT into Classification VALUES( 4,'Metal')

Now 2nd table is ‘CD’ which record details about particular ‘CD’ like cd’s title, compose_id, saleprice, averagecost

CREATE TABLE CD (
CD_ID integer NOT NULL PRIMARY KEY,
CD_Title varchar(40),
Composer_ID integer NOT NULL,
Classif_ID integer NOT NULL,
SalesPrice money,
AverageCost money)

INSERT into CD VALUES(2000,'John',100,1,16.99,6.99)
INSERT into CD VALUES(2001,'Chicago 16',107,1,14.99,5.99)
INSERT into CD VALUES(2002,'Chicago 17',107,1,14.99,5.99)
INSERT into CD VALUES(2003,'Chicago 18',107,1,14.99,5.99)
INSERT into CD VALUES(2004,'Greatest Hits',107,1,16.99,7.99)
INSERT into CD VALUES(2005,'Midnight',101,3,14.99,5.99)
INSERT into CD VALUES(2006,'Mode',115,3,14.99,5.99)
INSERT into CD VALUES(2007,'Ultra',115,3,15.99,5.99)
INSERT into CD VALUES(2008,'Mindcrime',102,4,14.99,5.99)
INSERT into CD VALUES(2009,'Empire',102,4,14.99,5.99)
INSERT into CD VALUES(2010,'Land',102,4,12.99,4.99)
INSERT into CD VALUES(2011,'Night',103,4,11.99,3.99)
INSERT into CD VALUES(2012,'Pyromania',103,4,14.99,5.99)
INSERT into CD VALUES(2013,'Hysteria',103,4,14.99,5.99)
INSERT into CD VALUES(2014,'Hits',103,4,13.99,4.99)
INSERT into CD VALUES(2015,'Hits 2',104,2,15.99,6.99)
INSERT into CD VALUES(2016,'Greatest',105,2,14.99,5.99)
INSERT into CD VALUES(2017,'Hits 3',106,1,13.99,5.99)
INSERT into CD VALUES(2018,'Deep',108,1,12.99,2.99)
INSERT into CD VALUES(2019,'Turning',109,1,14.99,5.99)
INSERT into CD VALUES(2020,'TheHits',109,1,16.99,7.99)
INSERT into CD VALUES(2021,'Cars',110,1,9.99,3.99)
INSERT into CD VALUES(2022,'Anthology',110,1,25.99,11.99)
INSERT into CD VALUES(2023,'City',110,1,14.99,5.99)
INSERT into CD VALUES(2024,'Rick',111,1,11.99,2.99)
INSERT into CD VALUES(2025,'Live',112,1,19.99,8.99)
INSERT into CD VALUES(2026,'Pat',113,1,16.99,6.99)
INSERT into CD VALUES(2027,'Big',114,1,14.99,5.99)
INSERT into CD VALUES(2028,'Hurting',114,1,11.99,3.99)
INSERT into CD VALUES(2029,'Vol 1',116,1,9.99,2.99)
INSERT into CD VALUES(2030,'Vol 2',116,1,9.99,2.99)


Now we are ready to fire queries with compute by
SELECT CD.Classif_ID,Classification.Classification,
CD.SalesPrice
FROM CD,Classification
WHERE CD.Classif_ID = Classification.Classif_ID
ORDER BY Classification.Classification
COMPUTE MIN(CD.SalesPrice) by Classification.Classification
Here we are selecting CD.Classif_ID,Classification.Classification,
CD.SalesPrice columns by joining two tables CD,Classification on
‘Classif_ID‘ but twist is we are adding Compute by clause.
Point to remember is Whatever column we are adding next to by in compute clause should be listed in Order by Clause so we have done that.
Here we compute MIN(CD.SalesPrice) which will be organized depending upon
It’s by clause columns here ‘Classification.Classification’.We will get group of resultset for each distict value of Classification.Classification and minimuim of ‘CD.SalesPrice’ computed bellow it,in that result set.

Here is Fraction of actual output you can see

We can find minimuim of ‘CD.SalesPrice’ for each distinct value of Classification.Classification by using group by clause also.
Lets see how we can do it.

SELECT
CD.Classif_ID,
Classification.Classification,
Min(CD.SalesPrice)MinPriceInClassification
FROM
CD,
Classification
WHERE
CD.Classif_ID = Classification.Classif_ID
GROUP BY
Classification.Classif_ID,CD.Classif_ID,Classification.Classification
The Output will be as follows

Let’s compare the two Group by & Compute By.First we look at two outputs above
You will notice that in Compute by Output w.r.t. Classif_ID value ‘3’ ,we got all the records having columns CD.Classif_ID, CD.SalesPrice and
Classification.Classification and at the base we got minimuim of ‘CD.SalesPrice’ in that result set.
on the contratory in group by output we got just one record for Classif_ID value ‘3’ with columns CD.Classif_ID,Classification.Classification and
Min(CD.SalesPrice)MinPriceInClassification

Secondly we notice that in both Compute by & group by we are banking on aggreagate functions in case this ‘Min()’ but the place where they appear is different in Group by it appears in Select List while in compute by they are not in select list but in compute by clause.

Now what are restriction in while compute by clause?
The restriction while using compute by clause is columns with ntext, text, or image data types cannot be include in compute by clause.