CODEWITHSUNDEEP
mysqlsqlpostgresql
Ali
Ali

Reputation: 450

Get result for all dates within a given range including non-existent dates

(UPDATED)
I have two tables like below

Table subscribers:

id | service_id | subscribe_date

1  | 1          | 2016-08-10
2  | 2          | 2016-08-09
3  | 2          | 2016-08-05
4  | 1          | 2016-08-03

Table services:

id | service_name

1  | test1          
2  | test2  
3  | test2

subscribers.service_id has a foreign key on services.id

I want to get a data from this tables like below : 

service_id | subscribe_date | count

1        | 2016-08-10       | 1  
1        | 2016-08-09       | 0
2        | 2016-08-10       | 0  
2        | 2016-08-09       | 1
3        | 2016-08-10       | 0  
3        | 2016-08-09       | 0

I trying to get this data with below query: 

SELECT COUNT(*), subscribe_date, service_id
FROM subscribers
    INNER JOIN services ON subscribers.service_id = services.id
WHERE subscribe_date BETWEEN '2016-08-09' AND '2016-08-10'
GROUP BY service_id, subscribe_date;

But I am not successful. I get the below result:

1        | 2016-08-10       | 1  
2        | 2016-08-09       | 1

Upvotes: 1

Views: 60

Answers (3)

1000111
1000111

Reputation: 13519

Here you go:

Since you don't have any calendar table so that we need to created all the dates between your given date range (inclusive) through a query. But like I said you need to agree on the terms & conditions of this query before using it. 

SELECT 
dateWiseServices.id AS service_id,
dateWiseServices.`Day` AS subscribed_date,
COALESCE(yourQuery.total,0) AS cnt

FROM 
(
    SELECT 
    S.id,
    dateTable.Day
    FROM 
    (
    SELECT ADDDATE('2016-08-09', INTERVAL @i:=@i+1 DAY) AS DAY
    FROM (
    SELECT a.a
    FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    JOIN (SELECT @i := -1) r1
    WHERE 
    @i < DATEDIFF('2016-08-10', '2016-08-09')
    ) AS dateTable
    CROSS JOIN Services S
) AS dateWiseServices

LEFT JOIN 
(
    SELECT COUNT(*) AS total, subscribe_date, service_id
    FROM subscribers
            INNER JOIN services ON subscribers.service_id = services.id
    WHERE subscribe_date BETWEEN '2016-08-09' AND '2016-08-10'
    GROUP BY service_id, subscribe_date
) AS yourQuery 
ON dateWiseServices.id = yourQuery.service_id AND dateWiseServices.`Day` = yourQuery.subscribe_date
ORDER BY dateWiseServices.id, dateWiseServices.`Day` DESC

Upvotes: 1

Lokesh Kumar Gaurav
Lokesh Kumar Gaurav

Reputation: 726

Please try this I hope this will help you..

SELECT service_id,subscribe_date,COUNT(service_id)
FROM subscribers WHERE subscribe_date BETWEEN '2016-08-09' AND '2016-08-10'
GROUP BY service_id, subscribe_date;

In case you trying to check foreign key also try below query.

SELECT subscribers.service_id,subscribers.subscribe_date,COUNT(subscribers.service_id)
FROM subscribers,services WHERE subscribers.service_id=services.id 
AND subscribe_date BETWEEN '2016-08-09' AND '2016-08-10' GROUP BY service_id, subscribe_date;

Upvotes: 0

Piotr Rogowski
Piotr Rogowski

Reputation: 3880

Instead inner join use left join

SELECT COUNT(*), subscribe_date, service_id 
FROM subscribers LEFT JOIN services ON subscribers.service_id = services.id 
GROUP BY service_id,subscribe_date;

Upvotes: 0

Related Questions