CODEWITHSUNDEEP
sqlsqlitedatetime
user1025064
user1025064

Reputation: 7

How do I find the busiest month using sqlite

I am trying to find the busiest month (which month had the most guests) with the CheckIn attribute. This is my table:

Reservation

I understand that I would have to use GROUP BY but I don't know how I would be able to separate the checkin dates by month. I tried MONTH() but sql says no such function. Please help.

Thanks in advance :)

Upvotes: 0

Views: 1950

Answers (3)

Ananth
Ananth

Reputation: 4397

SELECT count(ReservationNumber) FROM Reservation GROUP BY strftime('%m', CheckIN) ORDER BY count(ReservationNumber) DESC

Upvotes: 0

Veom
Veom

Reputation: 15

SQLite doesn't have a Date type (http://www.sqlite.org/datatype3.html). So i would guess it is storing is as a blob which would be an integer stored as epoch number, you can use http://www.epochconverter.com/ to convert the month you want into epoch and then search over the range between the start of the month and the beginning of the month.

Upvotes: 0

Marco
Marco

Reputation: 57593

I should try this:

SELECT strftime('%m', CheckIN) AS month, COUNT(GuestID) AS tot
FROM reservation
GROUP BY month
ORDER by tot DESC

Upvotes: 2

Related Questions