Consolidate rows based on criteria
-
Hi,
here is some sample data
CREATE TABLE #TESTING (acc_no varchar(20), number INT, c_name varchar(20), R_Value decimal(10,2), time_spent decimal(10,2) )
INSERT INTO #TESTING VALUES ('C1232' ,4445, 'Tom', 345.7, 43.56)
INSERT INTO #TESTING VALUES ('C1232' ,3456, 'Tom', 3454.7, 553.556)
INSERT INTO #TESTING VALUES ('C1232',6789, 'Thomas', 1345.7, 463.556)
INSERT INTO #TESTING VALUES ('C125632',1234, 'Will', 423.64, 233.77)
INSERT INTO #TESTING VALUES ('C125632',2345, 'William', 56.76, 77.89)
INSERT INTO #TESTING VALUES ('C125632',1345, 'Will', 444.56, 234.54)
INSERT INTO #TESTING VALUES ('C125632',12634, 'Will', 34.27, 112.56)
select * from #TESTING
basically what I would like is to sum the last two columns based on acc_no and return the most featured 'c_name' per acc_no.
The result fro the above would be :
CREATE TABLE #Result1 (acc_no varchar(20), number INT, c_name varchar(20), R_Value decimal(10,2), time_spent decimal(10,2) )
INSERT INTO #Result1 VALUES ('C1232' ,4445, 'Tom', 5146.1, 1060.672)
INSERT INTO #Result1 VALUES ('C125632' ,1234, 'Will', 959.23, 658.76)
select * from #Result1
Thanks a million
-
Probably a more efficient way, but this is the idea:
; WITH MyCTE (acc_no, c_name, cnt)AS
( SELECT acc_no
, c_name
, COUNT(c_name)
FROM #testing a
GROUP BY acc_no
, c_name
)
SELECT
t.acc_no
, c.c_name
, number_sum = SUM( t.number)
, r_value_sum = SUM( t.R_Value)
FROM #TESTING t
INNER JOIN mycte c
ON t.acc_no = c.acc_no
WHERE c.cnt = (SELECT MAX(d.cnt)
FROM MyCTE d
WHERE d.acc_no = c.acc_no
)
GROUP BY t.acc_no
, c.c_name
You need to count and get the max count of the names, by account and then join with the sum.
-
This was removed by the editor as SPAM
-
with cte1 as (select acc_no,number,c_name,
sum(R_Value) over(partition by acc_no) as R_Value,
sum(time_spent) over(partition by acc_no) as time_spent,
count(*) over(partition by acc_no,c_name) as cn
from #TESTING),
cte2 as (
select acc_no,number,c_name,R_Value,time_spent,
row_number() over(partition by acc_no order by cn desc,number desc) as rn
from cte1)
select acc_no,number,c_name,R_Value,time_spent
from cte2
where rn=1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks a million for the help everyone! Really appreciate it 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply