Grouping in SQL

[cestonina]

Hi there,

I'm currently creating a stored procedure. I would like to group my data by hour. Not sure which command to use.

My database source looks like the example below:

10/01/2005 01:25 Tom 10
10/01/2005 01:25 Tom 15
10/01/2005 01:30 John 25
10/01/2005 02:30 Tom 15
10/01/2005 02:31 John 25
10/01/2005 02:40 John 25

The output of the stored procedure should like something like this:

10/01/2005 01:00 Tom 25
10/01/2005 01:00 John 25
10/01/2005 02:00 Tom 15
10/01/2005 02:00 John 50

Appreciate any help reg. this matter. Thanks in advance.

Regards,
Chris

 

[rac2]

The essence of the solution is the DATEPART( unit_of_time, date_value) function.

SELECT DATEPART( hour, date_of_event) AS "Hour",
            SUM(amount_of_event) AS "Amount"
FROM MyTable
GROUP BY DATEPART( hour, date_of_event)

If the data spans more than one date then use additional DATEPART() expressions to separate year, month, and day. You can GROUP BY these expressions.

SELECT 
            DATEPART( year, date_of_event) AS "Year",
            DATEPART( month, date_of_event) AS "Month",
            DATEPART( day, date_of_event) AS "Day",
            DATEPART( hour, date_of_event) AS "Hour",
            SUM(amount_of_event) AS "Amount"
FROM MyTable
GROUP BY
            DATEPART( year, date_of_event) AS "Year",
            DATEPART( month, date_of_event) AS "Month",
            DATEPART( day, date_of_event) AS "Day",
            DATEPART( hour, date_of_event)

The tricky part might be reconstructing the date and hour for display. You would need to build a string from the date parts. That might be better done in the application you are using for presenting the data, although it can be done just as easily in SQL, it is ugly.