Nilesh Nilesh - 5 months ago 25
SQL Question

Aggregate Function on multiple columns in SQL Server

I have the following data in a #temp table:

Id code Fname CompanyId FieldName Value
----------------------------------------------------------------
465 00133 JENN WILSON 1 ERA 1573
465 00133 JENN WILSON 1 ESHIFTALLOW 3658
465 00133 JENN WILSON 1 NETPAY 51560


I want to do following operation i.e

One Row will be addition on two columns i.e
ERA + ESHIFTALLOW

Other Row will be subtraction & addition on three columns i.e
NETPAY - ERA + ESHIFTALLOW

I had tried using case statement in SQL Server.

Following is the output required

where
Field1= ERA + ESHIFTALLOW
&
Filed2=NETPAY - ERA + ESHIFTALLOW


Id code Fname CompanyId FieldName Value
----------------------------------------------------------------
465 00133 JENN WILSON 1 Field1 5231
465 00133 JENN WILSON 1 Filed2 46329


I had tried using SQL SERVER Case Statement but not getting proper output
SQL Query : Aggregate option in SQL Server CASE statement

Answer

I see at least 2 methods to get those results. A group by or a pivot

In the example below the 2 methods are shown.

CREATE TABLE #Temp (Id INT, code VARCHAR(5), Fname VARCHAR(20), CompanyId INT, FieldName VARCHAR(20), Value INT);

insert into #Temp (Id, code, Fname, CompanyId, FieldName, Value)
values 
(465,00133,'JENN WILSON',1,'ERA',1573),
(465,00133,'JENN WILSON',1,'ESHIFTALLOW',3658),
(465,00133,'JENN WILSON',1,'NETPAY',51560);

with Q AS (
  SELECT Id, code, Fname, CompanyId, 
  sum(case when FieldName = 'ERA' then Value end) as ERA,
  sum(case when FieldName = 'ESHIFTALLOW' then Value end) as ESHIFTALLOW,
  sum(case when FieldName = 'NETPAY' then Value end) as NETPAY
  from #Temp
  group by Id, code, Fname, CompanyId
)
select Id, code, Fname, CompanyId, 'Field1' as FieldName, (ERA +  ESHIFTALLOW) as Value from Q
union all
select Id, code, Fname, CompanyId, 'Field2', (NETPAY - ERA +  ESHIFTALLOW) from Q
;

with Q AS (
  SELECT Id, code, Fname, CompanyId, 
  (ERA +  ESHIFTALLOW) as Field1,
  (NETPAY - ERA +  ESHIFTALLOW) as Field2
  FROM (SELECT * FROM #Temp) s
  PIVOT ( SUM(VALUE) FOR FieldName IN (ERA, ESHIFTALLOW, NETPAY)) p
)
select Id, code, Fname, CompanyId, 'Field1' as FieldName, Field1 as Value from Q
union all
select Id, code, Fname, CompanyId, 'Field2', Field2 from Q
;

Note that SUM(VALUE) was used instead of MAX(VALUE). In this case it will yield the same results. It's just a choice really.

Comments