InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 00:06:55 GMT20http://www.sqlservercentral.com/Forums/Topic1347152-391-1.aspxThanks a million for the help everyone! Really appreciate it :)Mon, 20 Aug 2012 08:44:47 GMTmic.con87http://www.sqlservercentral.com/Forums/Topic1347152-391-1.aspx[code="sql"]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 cnfrom #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 rnfrom cte1)select acc_no,number,c_name,R_Value,time_spentfrom cte2where rn=1[/code]Mon, 20 Aug 2012 07:57:40 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1347152-391-1.aspxDoes the following help:[code="sql"];WITH SumPerAcct AS (SELECT acc_no, SUM(R_Value) AS Sum_R_Value, SUM(time_spent) AS Sum_time_spent FROM #TESTING GROUP BY acc_no), OccurrenceCount AS (SELECT acc_no, MAX(number) AS maxNumber, c_name, Count(c_name) NumerOfOccurence, ROW_NUMBER() OVER (PARTITION BY acc_no ORDER BY COUNT(c_name) DESC) Sequences FROM #TESTING GROUP BY acc_no, c_name), MaxOccurrence AS (SELECT acc_no, c_name FROM OccurrenceCount WHERE Sequences = 1) SELECT SumPerAcct.acc_no, OccurrenceCount.maxNumber, MaxOccurrence.c_name, SumPerAcct.Sum_R_Value, SumPerAcct.Sum_time_spent FROM SumPerAcct --ON SumPerAcct.acc_no = #TESTING.acc_no JOIN OccurrenceCount ON OccurrenceCount.acc_no = SumPerAcct.acc_no JOIN MaxOccurrence ON MaxOccurrence.acc_no = SumPerAcct.acc_no AND MaxOccurrence.c_name = OccurrenceCount.c_name[/code]Mon, 20 Aug 2012 07:51:56 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1347152-391-1.aspxProbably a more efficient way, but this is the idea:[code="sql"]; 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[/code]You need to count and get the max count of the names, by account and then join with the sum.Mon, 20 Aug 2012 07:50:14 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1347152-391-1.aspxHi,here is some sample data[code]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[/code]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 :[code]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[/code]Thanks a millionMon, 20 Aug 2012 07:18:58 GMTmic.con87