Click here to Skip to main content
15,305,361 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi
I have a table which contains three unique value

SQL
RId CID  Name Value
1    1   Test 2:15
1    1   Test 4:15
2    1   Name 1:00
2    1   Name 2:15



I want sql query to get result as

SQL
RID CID Name Value 
1   1    Test 6:30
2   1    Name 3:15


Unique in RID, CID, Name and Sum of value

value is in time format (01:15 format) but its nvarchar data type

Please help me to achive this

Thanks
Mohan
Posted
Updated 13-May-14 3:16am
v2
Comments
_Asif_ 13-May-14 6:50am
   
I believe this is a duplicate question
V Mohan 13-May-14 6:51am
   
No
V Mohan 13-May-14 6:54am
   
Sorry, I got the Sum of all values in a table , But my requirement is need some of Unique record values. Please help me

Try this

SQL
DECLARE @TBL TABLE 
(RID  INT,
 CID  INT, 
 NAME VARCHAR(50),
 Data VARCHAR(10));
 
INSERT INTO @TBL
(
	RID,
	CID,
	NAME,
	Data
)
SELECT 1, 1, 'Test', '2:15'
UNION ALL
SELECT 1, 1, 'Test', '4:15' UNION ALL
SELECT 2, 1, 'Name', '1:00' UNION ALL
SELECT 2, 1, 'Name', '2:15'

SELECT distinct TMP.RID, T.CID, T.NAME, cast(TMP.TOTAL_HOURS as varchar(10)) + ':' + cast(TMP.TOTAL_MINS as varchar(10))
FROM
(
select  RID, Sum(DATEPART(hh, CAST(data as DATETIME))) TOTAL_HOURS, Sum(DATEPART(MI, CAST(data as DATETIME))) TOTAL_MINS
from @TBL
group by RID
) TMP INNER JOIN @TBL T ON TMP.RID = T.RID
   
Make a query like following below
when Value is decimal,int
select RID, CID, Name, sum(Value) as Value from table_name group by RID, CID, Name

at the time datetime
SELECT RID, CID, Name, SUM(DATEDIFF(MINUTE, '0:00:00', convert(time, Value, 8))) as totalTime
FROM table_name 
GROUP BY RID, CID, Name
   
v3
Comments
V Mohan 13-May-14 7:07am
   
Thanks. But its working for me if the value is integer. My problem is the value field is can be time also so i use the below conversion to set sum of time

Convert(time,dateadd(s,SUM(( DATEPART(hh, td.value) * 3600 ) + ( DATEPART(mi, td.value) * 60 ) + DATEPART(ss, td.value)),0)) as Total

But it show the same values not the sum of the values. Please help me to resolve this
[no name] 13-May-14 7:15am
   
yes like
SUM( ( DATEPART(hh, td.value) * 3600 ) +
( DATEPART(mi, td.value) * 60 ) +
DATEPART(ss, td.value)
) AS total_sec
V Mohan 13-May-14 7:19am
   
Thanks, But it displays the seperate records as result like below
1 1 Test 2:15
1 1 Test 4:15
But i need result as follow
1 1 test 6:30

Please help
[no name] 13-May-14 7:42am
   
see it's updated
Run this


SQL
create table ##table(id bigint identity(1,1),name varchar(max) ,value varchar(max))

insert into ##table (name,value) values('King','2:15')
insert into ##table (name,value) values('KIng','4:15')
insert into ##table (name,value) values('Fisher','1:00')
insert into ##table (name,value) values('Fisher','2:15')

select *From ##table


Query

SQL
select name,convert(time,dateadd(s,SUM(
( DATEPART(hh, value) * 3600 ) +
( DATEPART(mi, value) * 60 ) +
 DATEPART(ss, value)),0)) From ##table group by name
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900