x

How to SUM the column for every day

Hi,

I have a table with 2 column 1) time - by 5 min and 2) sessions. i am using the below query to get the result

my question is what it the best way to SUM the sessions column for every day and get result like

 Date           |  Sessions
 2011-05-01     |  5
 etc,

please advice Thanks

the query:

 BEGIN TRAN
 SELECT
 CheckTimes.CheckTime 'Time' ,
 COUNT(SessionCount.StartTime) 'Concurrent Sessions'
 FROM
 ( SELECT TOP 100000
     d.DefDate + ' ' + h.DefHour AS CheckTime
   FROM
     ( SELECT
         '00:00:00' AS DefHour
       UNION ALL
       SELECT
         '00:05:00'
       UNION ALL
       SELECT
         '00:10:00'
       UNION ALL
       SELECT
         '00:15:00'
       UNION ALL
       SELECT
         '00:20:00'
       UNION ALL
       SELECT
         '00:25:00'
       UNION ALL
       SELECT
         '00:30:00'
       UNION ALL
       SELECT
         '00:35:00'
       UNION ALL
       SELECT
         '00:40:00'
       UNION ALL
       SELECT
         '00:45:00'
       UNION ALL
       SELECT
         '00:50:00'
       UNION ALL
       SELECT
         '00:55:00'
       UNION ALL
       SELECT
         '01:00:00' AS DefHour
       UNION ALL
       SELECT
         '01:05:00'
       UNION ALL
       SELECT
         '01:10:00'
       UNION ALL
       SELECT
         '01:15:00'
       UNION ALL
       SELECT
         '01:20:00'
       UNION ALL
       SELECT
         '01:25:00'
       UNION ALL
       SELECT
         '01:30:00'
       UNION ALL
       SELECT
         '01:35:00'
       UNION ALL
       SELECT
         '01:40:00'
       UNION ALL
       SELECT
         '01:45:00'
       UNION ALL
       SELECT
         '01:50:00'
       UNION ALL
       SELECT
         '01:55:00'
       UNION ALL
       SELECT
         '01:00:00' AS DefHour
       UNION ALL
       SELECT
         '01:05:00'
       UNION ALL
       SELECT
         '01:10:00'
       UNION ALL
       SELECT
         '01:15:00'
       UNION ALL
       SELECT
         '01:20:00'
       UNION ALL
       SELECT
         '01:25:00'
       UNION ALL
       SELECT
         '01:30:00'
       UNION ALL
       SELECT
         '01:35:00'
       UNION ALL
       SELECT
         '01:40:00'
       UNION ALL
       SELECT
         '01:45:00'
       UNION ALL
       SELECT
         '01:50:00'
       UNION ALL
       SELECT
         '01:55:00'
       UNION ALL
       SELECT
         '02:00:00' AS DefHour
       UNION ALL
       SELECT
         '02:05:00'
       UNION ALL
       SELECT
         '02:10:00'
       UNION ALL
       SELECT
         '02:15:00'
       UNION ALL
       SELECT
         '02:20:00'
       UNION ALL
       SELECT
         '02:25:00'
       UNION ALL
       SELECT
         '02:30:00'
       UNION ALL
       SELECT
         '02:35:00'
       UNION ALL
       SELECT
         '02:40:00'
       UNION ALL
       SELECT
         '02:45:00'
       UNION ALL
       SELECT
         '02:50:00'
       UNION ALL
       SELECT
         '02:55:00'
       UNION ALL
       SELECT
         '03:00:00' AS DefHour
       UNION ALL
       SELECT
         '03:05:00'
       UNION ALL
       SELECT
         '03:10:00'
       UNION ALL
       SELECT
         '03:15:00'
       UNION ALL
       SELECT
         '03:20:00'
       UNION ALL
       SELECT
         '03:25:00'
       UNION ALL
       SELECT
         '03:30:00'
       UNION ALL
       SELECT
         '03:35:00'
       UNION ALL
       SELECT
         '03:40:00'
       UNION ALL
       SELECT
         '03:45:00'
       UNION ALL
       SELECT
         '03:50:00'
       UNION ALL
       SELECT
         '03:55:00'
       UNION ALL
       SELECT
         '04:00:00' AS DefHour
       UNION ALL
       SELECT
         '04:05:00'
       UNION ALL
       SELECT
         '04:10:00'
       UNION ALL
       SELECT
         '04:15:00'
       UNION ALL
       SELECT
         '04:20:00'
       UNION ALL
       SELECT
         '04:25:00'
       UNION ALL
       SELECT
         '04:30:00'
       UNION ALL
       SELECT
         '04:35:00'
       UNION ALL
       SELECT
         '04:40:00'
       UNION ALL
       SELECT
         '04:45:00'
       UNION ALL
       SELECT
         '04:50:00'
       UNION ALL
       SELECT
         '04:55:00'
       UNION ALL
       SELECT
         '05:00:00' AS DefHour
       UNION ALL
       SELECT
         '05:05:00'
       UNION ALL
       SELECT
         '05:10:00'
       UNION ALL
       SELECT
         '05:15:00'
       UNION ALL
       SELECT
         '05:20:00'
       UNION ALL
       SELECT
         '05:25:00'
       UNION ALL
       SELECT
         '05:30:00'
       UNION ALL
       SELECT
         '05:35:00'
       UNION ALL
       SELECT
         '05:40:00'
       UNION ALL
       SELECT
         '05:45:00'
       UNION ALL
       SELECT
         '05:50:00'
       UNION ALL
       SELECT
         '05:55:00'
       UNION ALL
       SELECT
         '06:00:00' AS DefHour
       UNION ALL
       SELECT
         '06:05:00'
       UNION ALL
       SELECT
         '06:10:00'
       UNION ALL
       SELECT
         '06:15:00'
       UNION ALL
       SELECT
         '06:20:00'
       UNION ALL
       SELECT
         '06:25:00'
       UNION ALL
       SELECT
         '06:30:00'
       UNION ALL
       SELECT
         '06:35:00'
       UNION ALL
       SELECT
         '06:40:00'
       UNION ALL
       SELECT
         '06:45:00'
       UNION ALL
       SELECT
         '06:50:00'
       UNION ALL
       SELECT
         '06:55:00'
       UNION ALL
       SELECT
         '07:00:00' AS DefHour
       UNION ALL
       SELECT
         '07:05:00'
       UNION ALL
       SELECT
         '07:10:00'
       UNION ALL
       SELECT
         '07:15:00'
       UNION ALL
       SELECT
         '07:20:00'
       UNION ALL
       SELECT
         '07:25:00'
       UNION ALL
       SELECT
         '07:30:00'
       UNION ALL
       SELECT
         '07:35:00'
       UNION ALL
       SELECT
         '07:40:00'
       UNION ALL
       SELECT
         '07:45:00'
       UNION ALL
       SELECT
         '07:50:00'
       UNION ALL
       SELECT
         '07:55:00'
       UNION ALL
       SELECT
         '08:00:00' AS DefHour
       UNION ALL
       SELECT
         '08:05:00'
       UNION ALL
       SELECT
         '08:10:00'
       UNION ALL
       SELECT
         '08:15:00'
       UNION ALL
       SELECT
         '08:20:00'
       UNION ALL
       SELECT
         '08:25:00'
       UNION ALL
       SELECT
         '08:30:00'
       UNION ALL
       SELECT
         '08:35:00'
       UNION ALL
       SELECT
         '08:40:00'
       UNION ALL
       SELECT
         '08:45:00'
       UNION ALL
       SELECT
         '08:50:00'
       UNION ALL
       SELECT
         '08:55:00'
       UNION ALL
       SELECT
         '09:00:00' AS DefHour
       UNION ALL
       SELECT
         '09:05:00'
       UNION ALL
       SELECT
         '09:10:00'
       UNION ALL
       SELECT
         '09:15:00'
       UNION ALL
       SELECT
         '09:20:00'
       UNION ALL
       SELECT
         '09:25:00'
       UNION ALL
       SELECT
         '09:30:00'
       UNION ALL
       SELECT
         '09:35:00'
       UNION ALL
       SELECT
         '09:40:00'
       UNION ALL
       SELECT
         '09:45:00'
       UNION ALL
       SELECT
         '09:50:00'
       UNION ALL
       SELECT
         '09:55:00'
       UNION ALL
       SELECT
         '10:00:00' AS DefHour
       UNION ALL
       SELECT
         '10:05:00'
       UNION ALL
       SELECT
         '10:10:00'
       UNION ALL
       SELECT
         '10:15:00'
       UNION ALL
       SELECT
         '10:20:00'
       UNION ALL
       SELECT
         '10:25:00'
       UNION ALL
       SELECT
         '10:30:00'
       UNION ALL
       SELECT
         '10:35:00'
       UNION ALL
       SELECT
         '10:40:00'
       UNION ALL
       SELECT
         '10:45:00'
       UNION ALL
       SELECT
         '10:50:00'
       UNION ALL
       SELECT
         '10:55:00'
       UNION ALL
       SELECT
         '11:00:00' AS DefHour
       UNION ALL
       SELECT
         '11:05:00'
       UNION ALL
       SELECT
         '11:10:00'
       UNION ALL
       SELECT
         '11:15:00'
       UNION ALL
       SELECT
         '11:20:00'
       UNION ALL
       SELECT
         '11:25:00'
       UNION ALL
       SELECT
         '11:30:00'
       UNION ALL
       SELECT
         '11:35:00'
       UNION ALL
       SELECT
         '11:40:00'
       UNION ALL
       SELECT
         '11:45:00'
       UNION ALL
       SELECT
         '11:50:00'
       UNION ALL
       SELECT
         '11:55:00'
       UNION ALL
       SELECT
         '12:00:00' AS DefHour
       UNION ALL
       SELECT
         '12:05:00'
       UNION ALL
       SELECT
         '12:10:00'
       UNION ALL
       SELECT
         '12:15:00'
       UNION ALL
       SELECT
         '12:20:00'
       UNION ALL
       SELECT
         '12:25:00'
       UNION ALL
       SELECT
         '12:30:00'
       UNION ALL
       SELECT
         '12:35:00'
       UNION ALL
       SELECT
         '12:40:00'
       UNION ALL
       SELECT
         '12:45:00'
       UNION ALL
       SELECT
         '12:50:00'
       UNION ALL
       SELECT
         '12:55:00'
       UNION ALL
       SELECT
         '13:00:00' AS DefHour
       UNION ALL
       SELECT
         '13:05:00'
       UNION ALL
       SELECT
         '13:10:00'
       UNION ALL
       SELECT
         '13:15:00'
       UNION ALL
       SELECT
         '13:20:00'
       UNION ALL
       SELECT
         '13:25:00'
       UNION ALL
       SELECT
         '13:30:00'
       UNION ALL
       SELECT
         '13:35:00'
       UNION ALL
       SELECT
         '13:40:00'
       UNION ALL
       SELECT
         '13:45:00'
       UNION ALL
       SELECT
         '13:50:00'
       UNION ALL
       SELECT
         '13:55:00'
       UNION ALL
       SELECT
         '14:00:00' AS DefHour
       UNION ALL
       SELECT
         '14:05:00'
       UNION ALL
       SELECT
         '14:10:00'
       UNION ALL
       SELECT
         '14:15:00'
       UNION ALL
       SELECT
         '14:20:00'
       UNION ALL
       SELECT
         '14:25:00'
       UNION ALL
       SELECT
         '14:30:00'
       UNION ALL
       SELECT
         '14:35:00'
       UNION ALL
       SELECT
         '14:40:00'
       UNION ALL
       SELECT
         '14:45:00'
       UNION ALL
       SELECT
         '14:50:00'
       UNION ALL
       SELECT
         '14:55:00'
       UNION ALL
       SELECT
         '15:00:00' AS DefHour
       UNION ALL
       SELECT
         '15:05:00'
       UNION ALL
       SELECT
         '15:10:00'
       UNION ALL
       SELECT
         '15:15:00'
       UNION ALL
       SELECT
         '15:20:00'
       UNION ALL
       SELECT
         '15:25:00'
       UNION ALL
       SELECT
         '15:30:00'
       UNION ALL
       SELECT
         '15:35:00'
       UNION ALL
       SELECT
         '15:40:00'
       UNION ALL
       SELECT
         '15:45:00'
       UNION ALL
       SELECT
         '15:50:00'
       UNION ALL
       SELECT
         '15:55:00'
       UNION ALL
       SELECT
         '16:00:00' AS DefHour
       UNION ALL
       SELECT
         '16:05:00'
       UNION ALL
       SELECT
         '16:10:00'
       UNION ALL
       SELECT
         '16:15:00'
       UNION ALL
       SELECT
         '16:20:00'
       UNION ALL
       SELECT
         '16:25:00'
       UNION ALL
       SELECT
         '16:30:00'
       UNION ALL
       SELECT
         '16:35:00'
       UNION ALL
       SELECT
         '16:40:00'
       UNION ALL
       SELECT
         '16:45:00'
       UNION ALL
       SELECT
         '16:50:00'
       UNION ALL
       SELECT
         '16:55:00'
       UNION ALL
       SELECT
         '17:00:00' AS DefHour
       UNION ALL
       SELECT
         '17:05:00'
       UNION ALL
       SELECT
         '17:10:00'
       UNION ALL
       SELECT
         '17:15:00'
       UNION ALL
       SELECT
         '17:20:00'
       UNION ALL
       SELECT
         '17:25:00'
       UNION ALL
       SELECT
         '17:30:00'
       UNION ALL
       SELECT
         '17:35:00'
       UNION ALL
       SELECT
         '17:40:00'
       UNION ALL
       SELECT
         '17:45:00'
       UNION ALL
       SELECT
         '17:50:00'
       UNION ALL
       SELECT
         '17:55:00'
       UNION ALL
       SELECT
         '18:00:00' AS DefHour
       UNION ALL
       SELECT
         '18:05:00'
       UNION ALL
       SELECT
         '18:10:00'
       UNION ALL
       SELECT
         '18:15:00'
       UNION ALL
       SELECT
         '18:20:00'
       UNION ALL
       SELECT
         '18:25:00'
       UNION ALL
       SELECT
         '18:30:00'
       UNION ALL
       SELECT
         '18:35:00'
       UNION ALL
       SELECT
         '18:40:00'
       UNION ALL
       SELECT
         '18:45:00'
       UNION ALL
       SELECT
         '18:50:00'
       UNION ALL
       SELECT
         '18:55:00'
       UNION ALL
       SELECT
         '19:00:00' AS DefHour
       UNION ALL
       SELECT
         '19:05:00'
       UNION ALL
       SELECT
         '19:10:00'
       UNION ALL
       SELECT
         '19:15:00'
       UNION ALL
       SELECT
         '19:20:00'
       UNION ALL
       SELECT
         '19:25:00'
       UNION ALL
       SELECT
         '19:30:00'
       UNION ALL
       SELECT
         '19:35:00'
       UNION ALL
       SELECT
         '19:40:00'
       UNION ALL
       SELECT
         '19:45:00'
       UNION ALL
       SELECT
         '19:50:00'
       UNION ALL
       SELECT
         '19:55:00'
       UNION ALL
       SELECT
         '20:00:00' AS DefHour
       UNION ALL
       SELECT
         '20:05:00'
       UNION ALL
       SELECT
         '20:10:00'
       UNION ALL
       SELECT
         '20:15:00'
       UNION ALL
       SELECT
         '20:20:00'
       UNION ALL
       SELECT
         '20:25:00'
       UNION ALL
       SELECT
         '20:30:00'
       UNION ALL
       SELECT
         '20:35:00'
       UNION ALL
       SELECT
         '20:40:00'
       UNION ALL
       SELECT
         '20:45:00'
       UNION ALL
       SELECT
         '20:50:00'
       UNION ALL
       SELECT
         '20:55:00'
       UNION ALL
       SELECT
         '21:00:00' AS DefHour
       UNION ALL
       SELECT
         '21:05:00'
       UNION ALL
       SELECT
         '21:10:00'
       UNION ALL
       SELECT
         '21:15:00'
       UNION ALL
       SELECT
         '21:20:00'
       UNION ALL
       SELECT
         '21:25:00'
       UNION ALL
       SELECT
         '21:30:00'
       UNION ALL
       SELECT
         '21:35:00'
       UNION ALL
       SELECT
         '21:40:00'
       UNION ALL
       SELECT
         '21:45:00'
       UNION ALL
       SELECT
         '21:50:00'
       UNION ALL
       SELECT
         '21:55:00'
       UNION ALL
       SELECT
         '22:00:00' AS DefHour
       UNION ALL
       SELECT
         '22:05:00'
       UNION ALL
       SELECT
         '22:10:00'
       UNION ALL
       SELECT
         '22:15:00'
       UNION ALL
       SELECT
         '22:20:00'
       UNION ALL
       SELECT
         '22:25:00'
       UNION ALL
       SELECT
         '22:30:00'
       UNION ALL
       SELECT
         '22:35:00'
       UNION ALL
       SELECT
         '22:40:00'
       UNION ALL
       SELECT
         '22:45:00'
       UNION ALL
       SELECT
         '22:50:00'
       UNION ALL
       SELECT
         '22:55:00'
       UNION ALL
       SELECT
         '23:00:00' AS DefHour
       UNION ALL
       SELECT
         '23:05:00'
       UNION ALL
       SELECT
         '23:10:00'
       UNION ALL
       SELECT
         '23:15:00'
       UNION ALL
       SELECT
         '23:20:00'
       UNION ALL
       SELECT
         '23:25:00'
       UNION ALL
       SELECT
         '23:30:00'
       UNION ALL
       SELECT
         '23:35:00'
       UNION ALL
       SELECT
         '23:40:00'
       UNION ALL
       SELECT
         '23:45:00'
       UNION ALL
       SELECT
         '23:50:00'
       UNION ALL
       SELECT
         '23:55:00'
     ) AS h
   LEFT OUTER JOIN ( SELECT
                         CAST(CAST(GSH_START_DATETIME AS DATE) AS VARCHAR(10)) AS DefDate
                     FROM
                         SESSIONS_HISTORY_TB
                     WHERE
                         GSH_ERROR_CODE = 0
                         AND GSH_START_DATETIME >= '2011-05-01'
                         AND GSH_END_DATETIME <= '2011-05-02'
                     GROUP BY
                         CAST(GSH_START_DATETIME AS DATE)
                   ) AS d
   ON
     d.DefDate IS NOT NULL
 ) AS CheckTimes
 LEFT OUTER JOIN ( SELECT
                 GSH_START_DATETIME AS StartTime ,
                 GSH_END_DATETIME AS EndTime
               FROM
                 SESSIONS_HISTORY_TB
             ) AS SessionCount
 ON  SessionCount.StartTime <= CheckTimes.CheckTime
 AND SessionCount.EndTime > CheckTimes.CheckTime
 GROUP BY
 CheckTimes.CheckTime
 ORDER BY
 CheckTimes.CheckTime

 ROLLBACK 



more ▼

asked May 12, 2011 at 11:24 PM in Default

avatar image

confis
11 1 1 1

@Fatherjack, thank you for the edit you are braver than I, sir...I can't say I've ever rolled back a SELECT statement

May 14, 2011 at 10:37 AM Scot Hauder

@Scot Hauder I am not sure whether its better like this or as it was really.

Rollback - better safe than sorry!

May 14, 2011 at 12:08 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Well I can't really make sense of your query, but if you have data in the form of a table which contained a DateTime and an int, then you could sum them daily like this:

 SELECT Day, SUM(Value) FROM (
     SELECT DATEADD(d, datediff(d, 0, dateColumnName), 0) as Day, Value FROM table
 ) innerTable
 GROUP BY Day
more ▼

answered May 12, 2011 at 11:32 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

Thank you for your answer but i am a newbie in SQL can you please help and show me the all quay as its need to be

Thank you

May 13, 2011 at 02:46 AM confis
(comments are locked)
10|1200 characters needed characters left
  • To Matt

Just to add, if you're using SQL Server 2008 or later, then the distinct DATE and TIME datatypes may be right up your street. At least worth knowing about even if you can't use these in this scenario. You could then store just the date in one column, just the time in another column and then stick an index on date followed by time which will give you the performance benefits for scenarios like this - if you search by DATE or DATE+TIME, then the index could be used.

MSDN References: DATE, TIME

more ▼

answered May 12, 2011 at 11:42 PM

avatar image

AdaTheDev
871 1 3 6

very good point :)

May 13, 2011 at 12:21 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x426
x43

asked: May 12, 2011 at 11:24 PM

Seen: 2406 times

Last Updated: May 13, 2011 at 12:33 AM

Copyright 2016 Redgate Software. Privacy Policy