CODEWITHSUNDEEP
phpjquerymysqlsql
Pritesh Mahajan
Pritesh Mahajan

Reputation: 5154

create mysql query for fetching data in 3 days slotwise

I want to create a query for fetch all the data from database. But condition is get from before current month and data will convert into 3 days slot wise with count.Like if i have 12 month data and in July 1 to 3 date data will insert into 50 rows and from 3 to 6 date data will insert into 5 rows.How i fetch this things.That is the mail problem. I have wondered but nothing found related my problem. Is this possible.

Upvotes: 0

Views: 532

Answers (2)

Arnab Nandy
Arnab Nandy

Reputation: 6692

If you having timestamps as attribute type then might be this help you

SELECT count(*) as count
        FROM table
        AND
        TIMESTAMPDIFF(DAY,date,2014-08-01)<=3";

The date is the attribute of your column, TIMESTAMPDIFF will find difference between given date if it's under 3 days from 2014-08-01 records will show up. This way by just sending one date into this position you can find 3 slots of date from your table.

Upvotes: 0

VMai
VMai

Reputation: 10336

I assume you have a DATE or a DATETIME column, that marks the day of the insert, say the column created_at. Then you could get the desired result.

SELECT
    COUNT(*)
FROM
    your_table
WHERE
    created_at BETWEEN '2014-07-01' AND '2014-07-31 23:59:59'
GROUP BY
    (DAYOFMONTH(created_at) - 1) DIV 3

Explanation:

With the help of the DIV operator you get your 3-day-slots.

Note If there's a slot without rows, it won't be in the result. To get these too, you could use a LEFT JOIN with the maximum 11 slots.

Demo

Upvotes: 1

Related Questions