CODEWITHSUNDEEP
sqlsqliteandroid-sqlite
crazyPixel
crazyPixel

Reputation: 2330

Using nested select properly in a sqlite


I am trying to create a query to aggregate clicks from a db
contains an entry with a time and date for every click
I am stuck though since my query don't "compile"
I want to aggregate the clicks in between certain hours and display them in a catagory
(morning, noon, evening, night) How can I do that? - here is what I do as for now 

SELECT n
FROM click_history
Where (SELECT count(*) from click_history where click_history_hour BETWEEN '08:00:00' AND '12:00:00') as n

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If you want to aggregate by hour, then I would expect to see a group by. Maybe something like this:

select (case when click_history_hour < '08:00:00' then 'time1'
             when click_history_hour < '12:00:00' then 'time2'
             else 'time3'
        end) as whichtime, count(*) as n
from click_history
group by (case when click_history_hour < '08:00:00' then 'time1'
               when click_history_hour < '12:00:00' then 'time2'
               else 'time3'
        end);

Of course, the names of the periods and there definition is up to you.

Upvotes: 2

Related Questions