July 10, 2014 at 7:38 am
Hi everyone,
First of all, I would like to say thanks for your time reading this and I hope you could give me some advices on how to implement the following query.
I have a table with data as the following:
create table tblEmployee
(
id int primary key identity (1, 1),
salaryLastYear int,
salaryCurrentYear int
)
insert tblEmployee values (10, 20), (20, 30), (30, 40)
My expectation is to have the returned result set as the following:
id salaryLastYear salaryCurrentYear Flag
1 10 20 0
2 20 30 0
3 30 40 0
NULL 60 90 1
I have a solution as the following:
set statistics io on;
with cte as(
select *, sum(salaryLastYear) over() as [TotalSalaryLastYear],
sum(salaryCurrentYear) over() as [TotalSalaryCurrentYear] from tblEmployee)
select id, salaryLastYear, salaryCurrentYear, 0 from cte
union all
select top 1 null, TotalSalaryLastYear, TotalSalaryCurrentYear, 1 from cte;
But I don't want to apply the solution above because using CTE that will make scanning the table twice, and I also don't want to use Temp table because the real data is very large.
Do you have any other solutions?
Thanks you so much.
July 10, 2014 at 8:22 am
WITH ROLLUP is the option you need for a GROUP BY query.
DECLARE @tblEmployee AS table (
id int primary key identity (1, 1),
salaryLastYear int,
salaryCurrentYear int
)
insert into @tblEmployee
values (10, 20), (20, 30), (30, 40)
set statistics io on;
select id,
sum(salaryLastYear) as [TotalSalaryLastYear],
sum(salaryCurrentYear) as [TotalSalaryCurrentYear],
CASE WHEN id IS NULL THEN 1 ELSE 0 END AS flag
from @tblEmployee
GROUP BY id
WITH ROLLUP
Does that help?
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 10, 2014 at 8:57 am
Thank you so much, sgmunson
It has worked as my expectation :-). In the example above, I just simulated simple data to describe my problem. Tomorrow I will apply your idea for my real query and get back to you soon.
Regards,
Vinh
July 10, 2014 at 9:28 pm
Hi sgmunson and friends,
Your approach is ok, but I have a request that how to move the last row in your query to the first row position. Actually this is able to do easily in C# front end application but as a sql developer, I have no right to edit the front end web application, and the developers on front end also confirm that they could not change the current code because the current framework is complex.
My solution is with the returned result set from your query, I will use temp table to move the last row to the first row position, but the real data is very large (this is export function), so I could not use this approach.
Thank you for reading this, hope anyone could give me some ideas.
Regards
July 10, 2014 at 10:33 pm
mai.quangvinh (7/10/2014)
Hi sgmunson and friends,Your approach is ok, but I have a request that how to move the last row in your query to the first row position. Actually this is able to do easily in C# front end application but as a sql developer, I have no right to edit the front end web application, and the developers on front end also confirm that they could not change the current code because the current framework is complex.
My solution is with the returned result set from your query, I will use temp table to move the last row to the first row position, but the real data is very large (this is export function), so I could not use this approach.
Thank you for reading this, hope anyone could give me some ideas.
Regards
This is a piece of cake from a coding perspective: Simply add the ORDER BY clause for the id field, as follows:
DECLARE @tblEmployee AS table (
id int primary key identity (1, 1),
salaryLastYear int,
salaryCurrentYear int
)
insert into @tblEmployee
values (10, 20), (20, 30), (30, 40)
set statistics io on;
select id,
sum(salaryLastYear) as [TotalSalaryLastYear],
sum(salaryCurrentYear) as [TotalSalaryCurrentYear],
CASE WHEN id IS NULL THEN 1 ELSE 0 END AS flag
from @tblEmployee
GROUP BY id
WITH ROLLUP
ORDER BY id
Performance on your large dataset will depend on a number of factors, but an index on the id field would likely help.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply