Need to calculate average and sum of values

[SrinivasaC]

I have a data in the below format:

Date time column1 column2
03-07-2015 00:00 10 17
03-07-2015 00:30 16 62
03-07-2015 01:00 48 30
03-07-2015 01:30 19 67
03-07-2015 02:00 23 1

03-07-2015 23:30 67 34
02-07-2015 00:00 60 16
02-07-2015 00:30 19 10
02-07-2015 01:00 16 97
02-07-2015 01:30 49 64
02-07-2015 02:00 32 20

02-07-2015 23:30 38 74
01-07-2015 23:30 67 34
01-07-2015 00:00 60 16
01-07-2015 00:30 19 10
01-07-2015 01:00 16 97
01-07-2015 01:30 49 64
01-07-2015 02:00 32 20

01-07-2015 23:30 38 74

I had a data for 2 months in the above format, now I need to display the data in below format for the client, im not getting any clue for this.

03-07-2015  02-07-2015  01-07-2015  30-06-2015  29-06-2015  Sum Average

00:00 10 16 48 19 23 116 38.66666667
00:30 17 62 30 67 1 177 59
01:00 17 62 30 67 1 177 59
01:30 34 60 19 16 49 178 59.33333333
02:00 34 16 10 97 64 221 73.66666667

23:30 89 9 96 54 54 302 100.6666667

Unable to format data 😞
Please help me

Thanks in advance 🙂

[woodcock · Accepted solution]

I think your first row (header) and first column are reversed, right? Maybe this will get you what you need (hard to say for sure):

... | eventstats sum(column1) AS c1sum sum(column2) AS c2sum avg(column1) AS c1avg avg(column2) AS c2avg by Date | timechart span=1d first(column1) AS column1 first(column2) AS column2 first(c1sum) AS column1sum first(c2sum) AS column2sum first(c1avg) AS column1avg first(c2avg) AS column2avg by time

[acharlieh]

Often times, sums and averages can be calculated using commands like stats, chart, and timechart, by applying statistical functions to the results in your data. However it is not clear from your data and your expected output, how exactly you are wanting to transform your data. If you can explain in more detail both what you want and what you've tried maybe that will help the community understand what it is that you want to do exactly.

[SrinivasaC]

Thanks for quick reply,

I had a data for daily basis with half an hour intervals (so per day 48 events) will be generated in Database.
Getting data into index "myindex", using Splunk DB connect dbinput. On that index, with span period of 1 day, I have to calculate interval wise (00:00,00:30... so on) averages & summation for Column1 & Column2 on time interval basis.
i.e. for "0:00:00" on day1,day2 , day3... so on , values average and summation. and that should display in another column along with day1, day2, day3 values .. so on values with column1 & column2 values (as sample output provided in above question)

I have used stats, streamstats and timechart to achieve expected output, but not succeeded .