Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Consolidate rows based on criteria Expand / Collapse
Author
Message
Posted Monday, August 20, 2012 7:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:15 AM
Points: 103, Visits: 221
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
Post #1347152
Posted Monday, August 20, 2012 7:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:49 AM
Points: 33,072, Visits: 15,184
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1347179
Posted Monday, August 20, 2012 7:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 3,859, Visits: 5,001
Does the following help:
;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



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1347181
Posted Monday, August 20, 2012 7:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550

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



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1347188
Posted Monday, August 20, 2012 8:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:15 AM
Points: 103, Visits: 221
Thanks a million for the help everyone! Really appreciate it :)
Post #1347238
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse