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

confis gravatar 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

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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

+1 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][2]

[2]: http://msdn.microsoft.com/en-us/library/bb677243(v=SQL.100).aspx
more ▼

answered May 12, 2011 at 11:42 PM

AdaTheDev gravatar image

AdaTheDev
871 1 1 4

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x369
x24

asked: May 12, 2011 at 11:24 PM

Seen: 1692 times

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