0

Hi I have asked this question before but might nit have phrased it right hope someone can help me

i'm trying to get a SQLite query to count the occurrence of an value in a Rolling TimeFrame and return a distinct count

My Data looks something like this

---------DATA----------

TimeStamp         UserName   
2015-01-12 06:07:00     JamesG    
2015-01-12 06:07:00     JohnH
2015-01-12 06:10:00     JohnH
2015-01-12 06:10:00     James
2015-01-12 06:10:00     Dean
2015-01-12 06:10:00     Alicia
2015-01-12 06:12:00     Ann
2015-01-12 06:12:00     Dean
2015-01-12 06:12:00     JohnH

This is the result I would like

Result                      count
"2015-01-12 06:07:00"         2
"2015-01-12 06:10:00"         4
"2015-01-12 06:12:00"         3

With my current select I get a result but it doesn't consider Timestamp it generates 5min intervals

WITH RECURSIVE
min_time(t) AS (
SELECT datetime((strftime('%s',
(SELECT  MIN(TimeStamp)
FROM ConcurrentUsers)
) / 300) * 300,
'unixepoch')
),
max_time(t) AS (
SELECT datetime((strftime('%s',
(SELECT  MAX(TimeStamp)
FROM ConcurrentUsers)
) / 300) * 300,
'unixepoch')
),
intervals(t) AS (
SELECT t FROM min_time
UNION ALL
SELECT datetime(t, '+5 minutes')
FROM intervals
WHERE t <= (SELECT t FROM max_time)
)
SELECT t AS interval,
(SELECT COUNT(Distinct(strftime('%M',TimeStamp)))
FROM ConcurrentUsers
WHERE TimeStamp < datetime(intervals.t, '+5 minutes') AND TimeStamp >= intervals.t AS Count
FROM intervals where interval >= date('now')
ORDER BY date(interval) DESC

Any help would be appreciated.

I think I found the solution 

WITH C AS (SELECT DISTINCT TimeStamp FROM ConcurrentUsers)
SELECT Count(Distinct(UserName))TheCount,TimeStamp FROM ConcurrentUsers where TimeStamp >= date('now') 
Group by TimeStamp
ORDER BY date(TimeStamp) DESC
Dean Wilken
  • 21
  • 4
  • `DISTINCT` ...where in the query are you even selecting Distinct Count(*)` ? [SQL Basics Tutorial](http://www.w3schools.com/sql/sql_func_count.asp) – MethodMan Jan 26 '15 at 19:02
  • Hi Sorry I have redone this query 100 times, this query does not give me results based on my timestamp it generates intervals of 5 min – Dean Wilken Jan 26 '15 at 19:05
  • so does that mean you did it `100 times correctly || Incorrectly` what does that statement mean..? – MethodMan Jan 26 '15 at 19:06
  • ok I mean I tried 100 different ways that's why the statement does not have the distinct will update date it shortly "2015-01-26 00:00:00" "0" "2015-01-26 00:05:00" "0" "2015-01-26 00:10:00" "0" "2015-01-26 00:15:00" "0" "2015-01-26 00:20:00" "0" "2015-01-26 00:25:00" "0" "2015-01-26 00:30:00" "0" "2015-01-26 00:35:00" "0" "2015-01-26 00:40:00" "0" "2015-01-26 00:45:00" "0" "2015-01-26 00:50:00" "0" "2015-01-26 00:55:00" "0" "2015-01-26 01:00:00" "0" – Dean Wilken Jan 26 '15 at 19:09
  • Im not a SQL expert hence hoping someone who is could assist me with this not sure what i'm missing – Dean Wilken Jan 26 '15 at 19:18
  • that's why there is a thing called `Google` I am not an Sql Expert either but when I want to find examples on how to do something I do a google search and read through the examples as well as try to understand what it is I am not only `writing but also reading` please do not expect others to just provide you an answer and `Im not a Sql expert` is not an excuse.. if someone provides you a working solution how will you be able to maintain it if requested to make changes if you don't try to write something on your own as well as understand what it is that you've written.?? – MethodMan Jan 26 '15 at 19:23
  • I hear you and that is not my intension I have googled and googled believe me this is the last resort – Dean Wilken Jan 26 '15 at 19:33
  • well I posted a link and when you do a google search it's as easy as `Sql Server Select Distinct Examples` – MethodMan Jan 26 '15 at 19:35
  • I hear you but my question isn't how to do a distinct I want a return of multiple Counts based on Timestamp with distinct values in a certain column in that Timestamp – Dean Wilken Jan 26 '15 at 19:43
  • Why is the result `2` for `06:12`? – CL. Jan 26 '15 at 21:29
  • @CL im doing it on the timestamp I get the data – Dean Wilken Jan 26 '15 at 21:32
  • But I must thank you @CL With the help you gave me last time I think I figured it out with my current Req. if you look at the updated post with the solution at the end of my question if you can just check that for me it would be great ! – Dean Wilken Jan 26 '15 at 21:34
  • There are three rows with `06:12`. Why `2`? – CL. Jan 26 '15 at 21:40
  • Sorry Typo you are right there are 3 Rows – Dean Wilken Jan 26 '15 at 21:42
  • I typed up what I wanted from the Query before I got the solution fixed it now in the Question – Dean Wilken Jan 26 '15 at 21:42
  • Similar to http://stackoverflow.com/questions/8856266/linq-aggregate-and-group-by-periods-of-time using count. – Jason Sturges Jan 26 '15 at 21:55

0 Answers0