sql server - How to sum/group by rows that w/ hour format(120:15:10) in C# like in SQL - SELECT SUM (col1), col 2, col3 GROUP BY col2, col3 -

sql server - How to sum/group by rows that w/ hour format(120:15:10) in C# like in SQL - SELECT SUM (col1), col 2, col3 GROUP BY col2, col3 -

problem: have sql database c# widowsform app datagridview show results. import info csv streamreader. , need create select sum([col1]), [col2], [col3] grouping [col2], [col3]

but column need sum nvarchar representing hours have format "000:00:00"

example:

db columns: occurrence_number|solution_group|timein_sgroup

sql comamand:select sum(timein_sgroup), occurence_number, solution_group grouping occurence_number, solution_group

resume: need sum of hours in column(occurence_number) results grouped 2 columns(occurence_number , solution_group)

it's scheme calculates total time back upwards grouping stayed working with/in occurrence.

i need result = how much hours occurence stayed in solution group:

occnum | groupnamelvl | total hours(sum) ------------------------------------------ occ001 | sgrouplevel2 | 120:10:10 occ002 | sgrouplevel1 | 20:30:15 occ001 | sgrouplevel1 | 05:15:00 occ010 | sgrouplevel3 | 105:05:00

i saw lot of topics sum hr showing result more 24 hours solutions not work when have alredy column rows info this:

occnum | groupnamelvl | hours sum ------------------------------------------ occ001 | sgrouplevel2 | 120:15:10 occ001 | sgrouplevel2 | 30:10:40 occ001 | sgrouplevel2 | 110:30:00

how sum column , show result like:

occnum | groupnamelvl | total_hours ------------------------------------------ occ001 | sgrouplevel2 | 260:55:50

i utilize work info mior tahn 24h , result can bem more 24 minor 99:

select convert(nvarchar, dateadd(minute, sum( datepart(second,convert(datetime,[colunm],8)) + 60 * datepart(minute,convert(datetime,[colunm],8)) + 3600 * datepart(hour,convert(datetime,[colunm],8)) ) /60, '00:00:00'), 108) [total] tb

c# part query:

database1dataset ds = new database1dataset(); string strsql = "that query"; sqlceconnection conn = new sqlceconnection(@"data source=\app_sqlce35\app\data\database1.sdf"); sqlcedataadapter da = new sqlcedataadapter(strsql, conn); seek { conn.open(); ds.tables.add("historico"); ds.tables["historico"].beginloaddata(); da.fill(ds.tables["historico"]); ds.tables["historico"].endloaddata(); tb_histstatusdatagridview.datasource = ds.tables["historico"];

new code help @asadali works fine can´t grouping by:

sqlceconnection conn = new sqlceconnection(@"data source=\app_sqlce35\data\database1.sdf"); sqlcecommand cmdselect = new sqlcecommand("select [col0], [col1], [col2], tb_histstatus [col6] <>'';", conn); seek { conn.open(); sqlcedatareader res = cmdselect.executereader(); list<string> times = new list<string>(); while (res.read()) { times.add((string)res.getvalue(2)); } int[] result = new int[3]; foreach (string time in times) { string[] parts = time.split(':'); (int = 0; < parts.length; i++) { result[i] += convert.toint32(parts[i]); } } timespan ts = timespan.fromseconds(result[1] * 60 + result[2]); result[0] += ts.hours; result[1] = ts.minutes; result[2] = ts.seconds; string resultstring = string.join(":", result); messagebox.show("total: " + resultstring); }

here illustration of i'd do:

int[] result = new int[3]; //assuming format in op string[] times = { "120:15:10", "30:10:40", "110:30:00" }; foreach (string time in times) { string[] parts = time.split(':'); (int = 0; < parts.length; i++) { result[i] += convert.toint32(parts[i]); } } timespan ts = timespan.fromseconds(result[1] * 60 + result[2]); result[0] += ts.hours; result[1] = ts.minutes; result[2] = ts.seconds; string resultstring = string.join(":", result);

output: 260:55:50

edit: i've updated code match right format.

edit 2: here's how you'd utilize it.

sqlceconnection conn = new sqlceconnection(@"data source=\app_sqlce35\data\database1.sdf"); sqlcecommand cmdselect = new sqlcecommand("select [col0], [col1], [col2], [col3], [col4], [col5], [col6] tb_histstatus [col6] <>'';", conn); seek { conn.open(); sqlcedatareader res = cmdselect.executereader(); list<string> times = new list<string>(); while (res.read()) { times.add((string)res.getvalue(6)); } int[] result = new int[3]; foreach (string time in times) { string[] parts = time.split(':'); (int = 0; < parts.length; i++) { result[i] += convert.toint32(parts[i]); } } timespan ts = timespan.fromseconds(result[1] * 60 + result[2]); result[0] += ts.hours; result[1] = ts.minutes; result[2] = ts.seconds; string resultstring = string.join(":", result); messagebox.show("total: " + resultstring); }

c# sql-server