x
login about faq Site discussion (meta-askssc)

How to SUM the column for every day

0

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 '11 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 '11 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 '11 at 12:08 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
▼ Viewable by all users
2 answers: sort voted first ▼
0

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 '11 at 11:32 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 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 '11 at 02:46 AM confis
(comments are locked)
10|1200 characters needed characters left
▼ Viewable by all users
0

+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

more ▼

answered May 12 '11 at 11:42 PM

AdaTheDev gravatar image

AdaTheDev
871 ● 1 ● 1 ● 4

very good point :)

May 13 '11 at 12:21 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
▼ Viewable by all users
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



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x322
x18

asked: May 12 '11 at 11:24 PM

Seen: 1053 times

Last Updated: May 13 '11 at 12:33 AM

Related Questions

how would i find the sum of a group from another sum of a group eg.

ERROR #1064 --WHEN TRYING TO RUN A QUERY WITH JOINS

Need SQL script for determining difference in pricing for records in table with same key but different dates

exec command fail when it execute a variable

plz help me in this query

query for count

Elaborate the error ORA-06531 while running the below query ?

Alternative to UNION ALL

please help with this query still no response

UPDATE TASKS with TABLE B data with TABLE A JOIN

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.

AnswerHubEnterprise
Social Q&A