UPDATE Statement with Aggregate function in WHERE condition

I seen many places developer get confused about how to use AGGREGATE function in WHERE condition with UPDATE statement. Suppose I have one table with user name and percentage. I want to update percentage if one user’s percentage sum is greater or lower than some value.

–table used

create table test11

(

uName varchar(20),

percentage int

)

–data used

INSERT INTO test11

SELECT ‘RITESH’,90 UNION ALL

SELECT ‘RAJAN’,75 UNION ALL

SELECT ‘RITESH’,80 UNION ALL

SELECT ‘ALKA’,70

–update query

update test11 set percentage=100

where uname=‘Ritesh’

AND

percentage in

(

select percentage from test11

WHERE uname=‘Ritesh’ group by percentage having sum(percentage)>70

)

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Advertisements

PIVOT –Fixed Column Cross Tab Query in Microsoft SQL Server 2005

Sometime we may need to see some data of one column as a column header and aggregated results in those columns as a data. There are many programmatic ways to cater this need. Herewith, I am going to explained Fixed Column Cross Tab results with PIVOT. I will write few more articles for other ways of doing the same task.

Here is the table, I will be using for demo.

Create Table SalesSummaryOfRegions

(

Item VARCHAR(10) NOT NULL,

State VARCHAR(10) NOT NULL,

TotalSales INT NOT NULL

)

INSERT INTO SalesSummaryOfRegions

SELECT ‘LAPTOP’,‘CA’,100 UNION ALL

SELECT ‘LAPTOP’,‘NJ’,1200 UNION ALL

SELECT ‘ADAPTER’,‘CA’,910 UNION ALL

SELECT ‘MOUSE’, ‘NY’,1100 UNION ALL

SELECT ‘MOUSE’,‘NY’,2000

Now, I want to get list of all the Items of NJ and CA with its total and average sales. It seems quiet easy as we can use simple GROUP BY clause and SUM aggregate function. But what if I want to see four column named Item, CA, NJ, Average.

Item column should contain all the Items we used to sell. CA column contain total of each item we sold in CA state in respective item’s row and same with NJ. Average column should contain average of particular item sold for both the state. Not you can say, its bit challenging. Yes, it is, if you are going to do it with sub query logic or cursor. But with the help of PIVOT in Microsoft SQL Server 2005, it is very simple. Have a look at following query.

SELECT Item,CA,NJ,(isnull(CA,0)+isnull(NJ,0))/2 as ‘Average’ FROM SalesSummaryOfRegions

PIVOT

(

sum(TotalSales) FOR state in (CA,NJ)

)AS pivo

In above query, we gave Item from SalesSummaryOfRegions table in select list but we don’t have CA and NJ column in our table. Rest assure, it won’t show you error as it was not written by mistake, it’s a use of PIVOT. You can see I use one aggregate function after PIVOT words in above query and it was used for state column. As will be showing average also, so there is one possibility of NULL value as respective value so I want to make sure that NULL value will be treated as 0 that is why I have used isnull() function.

Reference: Ritesh Shah

Complex Aggregate Function example in SQL-Server 2005 with SubQuery:

Complex Aggregate Function example in SQL-Server 2005 with SubQuery:

In general analysis and reporting purpose, sometime we need to find department name, total salary paid to each department, total number of project department is working for and average salary being paid to each department. This is data administrative department may frequently asked for. Let us have a practical example of the same with GROUP BY clause, AGGREGATE function and SubQuery. For the demonstration purpose, we need to create following three tables and have to insert some data in it.

Create Table Department

(

deptNo INT IDENTITY(1,1) NOT NULL,

deptName VARCHAR(15) NOT NULL,

Description VARCHAR(50) NULL

)

Create Table Employee

(

empNo INT IDENTITY(1,1) NOT NULL,

empName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL,

Salary INT NOT NULL

)

Create Table Project

(

projNo INT IDENTITY(1,1) NOT NULL,

projName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL

)

Create Table Department

(

deptNo INT IDENTITY(1,1) NOT NULL,

deptName VARCHAR(15) NOT NULL,

Description VARCHAR(50) NULL

)

INSERT INTO Department(deptName,Description)

SELECT ‘MIS1’,‘MIS1’ UNION ALL

SELECT ‘MIS2’,‘MIS2’

Create Table Employee

(

empNo INT IDENTITY(1,1) NOT NULL,

empName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL,

Salary INT NOT NULL

)

INSERT INTO Employee(empName,deptNo,Salary)

SELECT ‘Ritesh’,1,50000 UNION ALL

SELECT ‘Bihag’,2,29000 UNION ALL

SELECT ‘Rajan’,1,30000 UNION ALL

SELECT ‘Alka’,2,19000

Create Table Project

(

projNo INT IDENTITY(1,1) NOT NULL,

projName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL

)

INSERT INTO Project(projName,deptNo)

SELECT ‘A’,1 UNION ALL

SELECT ‘B’,2 UNION ALL

SELECT ‘C’,1

Now, we are going to find department name, salary paid to each department, project – department is working for and last but not least average salary paid to each department in order to find efficiency of department.

SELECT Dept.deptname, emp.salary, prj.[Count],emp.salary/prj.[count] as ‘AverageSalary/DEPT’

FROM Department Dept,

(SELECT deptNo, sum(salary) salary FROM Employee GROUP BY deptNo) emp,

(SELECT deptNo, count(*) [Count] FROM Project GROUP BY deptNo) prj

WHERE Dept.deptNo=emp.deptNo and emp.deptNo=prj.deptNo

Above query has used simple SUM and COUNT aggregate function with subquery as table. As I gave example for Aggregate function, let me write down something more for an Aggregate function for those who are new to it.

Aggregate function will return only single row with computed value which summarizes the original result set. It will not return all row in return set. When you use aggregate function first result set returns the row set based on FROM clause and WHERE condition and then filtered records only will be aggregated.

Aggregate functions are expressions so it is mandatory to give column name to it explicitly or else it will return NULL as a column name, if you observe we gave name to all aggregated column like ‘AverageSalary/DEPT’. If we wouldn’t have done that, it would have return NULL as column name.

Aggregate function will not handle precision of numeric column, it is completely based on the source column. However, you can convert the results to other data type as well.

Reference: Ritesh Shah