Solved

sql query grouping by time

Posted on 2014-03-06
2
332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

688 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