Interval by 4 using sql - Mysql
I have a table and I want the data to be an interval of 4 or when I use modulo write I didn't expect PFB `
SELECT (DATE_FORMAT(subscribed_from, '%Y-%m')) AS date_ FROM subscription
WHERE operator = 'tim'
AND DATE_FORMAT(subscribed_from, '%Y-%m-%d') BETWEEN '2013-01-01' AND '2014-12-31'
GROUP BY (DATE_FORMAT(subscribed_from, '%Y-%m'));
will display an entry like this
2013-01 2013-02 2013-03 2013-04 2013-05 2013-06 2013-07 2013-08 2013-09
I only want to take a data interval of 4, this below is the entry I was expecting.
2013-01 2013-05 2013-09 2014-02
and also for an interval of 2, this below entry is what I was expecting
2013-01 2013-03 2013-05 2013-07 2013-09
if i use modulo % 2
it will start from 2013-01 and start going to 2, but the problem is if the range i want to start from 2013-02, 02 itself is not mapped to the result. so if the where clause month starts at 2, it will be given an interval such as 2,4,6,8,10,12
source to share
SELECT date_, SUM(the_metric_you_want_to_aggregate)
FROM (
SELECT 4*FLOOR(
(DATE_FORMAT(subscribed_from, '%Y%m') - 201301)
/4) AS date_,
the_metric_you_want_to_aggregate
FROM subscription
WHERE operator = 'tim'
AND subscribed_from BETWEEN 20130101000000 AND 201412315959
) AS ilv
GROUP BY date_
(where 201301 is the year / month of the start of the range you select, assuming it is a link for a 4 month aggregation)
Note that including column references in the ( ...DATE_FORMAT(subscribed_from, '%Y-%m-%d') BETWEEN...
) function prevents the use of indexes.
source to share
You must use variables. Here's a sample for spacing of 4.
SET @row_number:=0;
SELECT date_ from (
SELECT (DATE_FORMAT(subscribed_from, '%Y-%m')) AS date_,@row_number:=@row_number+1 FROM subscription
WHERE operator = 'tim' AND DATE_FORMAT(subscribed_from, '%Y-%m-%d') BETWEEN '2013-01-01' AND '2014-12-31'
GROUP BY (DATE_FORMAT(subscribed_from, '%Y-%m'))
) as tbl where @row_number % 4=0;
source to share
let says I am using this method to generate intevals, but I want the starting number from my input, let it say it starts with 4 and if the put condition% 4 should be output 4, 8, 12 ... ...
enter code here
SET @row:=0;
SELECT *
FROM (
SELECT
@row := @row +1 AS rownum
FROM (
SELECT @row) r, subscription
) ranked
WHERE rownum %4 = 1
source to share