Solved

sql query grouping by time

Posted on 2014-03-06
2
330 Views
Last Modified: 2014-03-08
Dtime                                    points
2014-02-01 08:00                   .3
2014-02-01 08:15                   .2
2014-02-02 08:00                   .6
2014-02-03 08:00                   .9
2014-02-02  08:15                  .85
2014-02-04  08:00                  .98

I want to group by the time and add the ones that are over .8 and divide by the total
so for above example it will be
8:00----2/4
8:15---1/2
0
Comment
Question by:Angela4eva
2 Comments
 
LVL 9

Expert Comment

by:DrewKjell
ID: 39910790
select DATEPART(hh, dtime) as hour, DATEPART(mm,dtime), SUM(CASE WHEN points >.8 then 1 else 0 end))/count('s')
from [table]
group by DATEPART(hh, dtime) as hour, DATEPART(mm,dtime)

Open in new window

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39911120
SELECT
    DATEADD(MINUTE, DATEDIFF(MINUTE, 0, Dtime) / 15 * 15, 0) AS DTime,
    SUM(CASE WHEN points > 0.8 THEN 1 ELSE 0 END) / COUNT(*) AS points
FROM ...
GROUP BY     DATEADD(MINUTE, DATEDIFF(MINUTE, 0, Dtime) / 15 * 15, 0)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question