Skip to main content
Added 1st update with an example
Source Link
NineCattoRules
  • 173
  • 1
  • 4
  • 12

I'm stuck, this job seems too complicated for me I hope in some goodness help here!

So this is my table views:

+---+--------------------+
| id|                time|
+---+--------------------+
| 1 | 2016-10-06 10:13:11|
| 2 | 2016-10-07 15:33:31|
| 3 | 2016-10-08 20:13:13|
| 4 | 2016-10-09 09:16:21|
| 5 | 2016-10-10 18:23:17|
| . |                 ...|
+---+--------------------+

I need to select in array the rows count of each day for the last 2 weeks and compare them (so I need to compare monday of current week and previous week).

So I tried:

SELECT 'last week''last_week' AS week, COUNT(*) AS rows 
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL  6 DAY 
      AND `time` < CURDATE() + INTERVAL  1 DAY
  UNION ALL
    SELECT 'previous week''previous_week' AS week, COUNT(*) AS rows 
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL 13 DAY
      AND `time` < CURDATE() - INTERVAL  6 DAY;

But it counts rows of each weeks and not single days. Yes, I can add eg. AND DAYOFWEEK(time) = 7; to the query, but in this case I have to declared it for seven times.

Basically I need something like:

+---------------+--------------------+
|          week | 1| 2| 3| 4| 5| 6| 7|
+---------------+--+--+--+--+--+--+--+
|     last_week | 1| 5| 3|15|12| 1| 3|
| previous_week |10|15|34|22|55|78|99|
+---------------+--+--+--+--+--+--+--+

Where 1 is Monday, 7 Sunday.

I'm stuck, this job seems too complicated for me I hope in some goodness help here!

So this is my table views:

+---+--------------------+
| id|                time|
+---+--------------------+
| 1 | 2016-10-06 10:13:11|
| 2 | 2016-10-07 15:33:31|
| 3 | 2016-10-08 20:13:13|
| 4 | 2016-10-09 09:16:21|
| 5 | 2016-10-10 18:23:17|
| . |                 ...|
+---+--------------------+

I need to select in array the rows count of each day for the last 2 weeks and compare them (so I need to compare monday of current week and previous week).

So I tried:

SELECT 'last week' AS week, COUNT(*) AS rows 
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL  6 DAY 
      AND `time` < CURDATE() + INTERVAL  1 DAY
  UNION ALL
    SELECT 'previous week' AS week, COUNT(*) AS rows 
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL 13 DAY
      AND `time` < CURDATE() - INTERVAL  6 DAY;

But it counts rows of each weeks and not single days. Yes, I can add eg. AND DAYOFWEEK(time) = 7; to the query, but in this case I have to declared it for seven times.

I'm stuck, this job seems too complicated for me I hope in some goodness help here!

So this is my table views:

+---+--------------------+
| id|                time|
+---+--------------------+
| 1 | 2016-10-06 10:13:11|
| 2 | 2016-10-07 15:33:31|
| 3 | 2016-10-08 20:13:13|
| 4 | 2016-10-09 09:16:21|
| 5 | 2016-10-10 18:23:17|
| . |                 ...|
+---+--------------------+

I need to select in array the rows count of each day for the last 2 weeks and compare them (so I need to compare monday of current week and previous week).

So I tried:

SELECT 'last_week' AS week, COUNT(*) AS rows 
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL  6 DAY 
      AND `time` < CURDATE() + INTERVAL  1 DAY
  UNION ALL
    SELECT 'previous_week' AS week, COUNT(*) AS rows 
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL 13 DAY
      AND `time` < CURDATE() - INTERVAL  6 DAY;

But it counts rows of each weeks and not single days. Yes, I can add eg. AND DAYOFWEEK(time) = 7; to the query, but in this case I have to declared it for seven times.

Basically I need something like:

+---------------+--------------------+
|          week | 1| 2| 3| 4| 5| 6| 7|
+---------------+--+--+--+--+--+--+--+
|     last_week | 1| 5| 3|15|12| 1| 3|
| previous_week |10|15|34|22|55|78|99|
+---------------+--+--+--+--+--+--+--+

Where 1 is Monday, 7 Sunday.

Source Link
NineCattoRules
  • 173
  • 1
  • 4
  • 12

How can I compare each single day of last two weeks in MySQL

I'm stuck, this job seems too complicated for me I hope in some goodness help here!

So this is my table views:

+---+--------------------+
| id|                time|
+---+--------------------+
| 1 | 2016-10-06 10:13:11|
| 2 | 2016-10-07 15:33:31|
| 3 | 2016-10-08 20:13:13|
| 4 | 2016-10-09 09:16:21|
| 5 | 2016-10-10 18:23:17|
| . |                 ...|
+---+--------------------+

I need to select in array the rows count of each day for the last 2 weeks and compare them (so I need to compare monday of current week and previous week).

So I tried:

SELECT 'last week' AS week, COUNT(*) AS rows 
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL  6 DAY 
      AND `time` < CURDATE() + INTERVAL  1 DAY
  UNION ALL
    SELECT 'previous week' AS week, COUNT(*) AS rows 
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL 13 DAY
      AND `time` < CURDATE() - INTERVAL  6 DAY;

But it counts rows of each weeks and not single days. Yes, I can add eg. AND DAYOFWEEK(time) = 7; to the query, but in this case I have to declared it for seven times.