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

Perform a count on a unique ID and sum two columns Expand / Collapse
Author
Message
Posted Tuesday, August 21, 2012 12:53 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, August 21, 2014 8:41 AM
Points: 110, Visits: 232
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',12344, 'Will', 34.27, 112.56)


select * from #TESTING

I basically need to perform a count on acc_no and sum R_Value and time_spent. I can drop column number. The column c_name may have similar names like Tom, Thomas etc. I only require one result ie c_name to be returned per acc_no and it doesn't matter which one.

Here is the result set I would like

CREATE TABLE #Result1 (acc_no varchar(20), count INT, c_name varchar(20), R_Value decimal(10,2), time_spent decimal(10,2) )

INSERT INTO #Result1 VALUES ('C1232' ,3, 'Tom', 5146.1, 1060.672)
INSERT INTO #Result1 VALUES ('C125632' ,4, 'Will', 959.23, 658.76)

select * from #Result1

I'm using a weird SQL called interactive SQL. Row_Number() doesnt work but dense_rank() does just in case you use an analytical function in your solution.

I tried the following code but it returns multiple records.

elect acc_no, count(acc_no) as count, sum(R_Value ) R_Value_1, sum(time_spent ) time_spent_1 
from #TESTING
group by acc_no, R_Value , time_spent

Thanks a million!!!
Post #1347589
Posted Tuesday, August 21, 2012 1:02 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: Yesterday @ 3:25 AM
Points: 3,417, Visits: 5,328
Will this work for you?

SELECT acc_no, [count]=COUNT(number), c_name=MAX(c_name)
, R_Value=SUM(R_Value), time_spent=SUM(time_spent)
FROM #TESTING
GROUP BY acc_no





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1347593
Posted Tuesday, August 21, 2012 1:51 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, August 21, 2014 8:41 AM
Points: 110, Visits: 232
Thanks so much!!! Appreciate the help!!!
Post #1347619
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse