CODEWITHSUNDEEP
mysql
Ravi Teja
Ravi Teja

Reputation: 119

Get count(table1_id) based on max(table2_date) that is equal to given date. But each table1_id has many table2_dates

I have two tables with the following data

Table 1: customers

|---|-----|
|id |name |
|---|-----|
|1  |Bob  |
|---|-----|
|2  |Alice|
|---|-----|

Table 2: subscriptions

|---|------------|-------------|
|id |customer_id |end_date     |
|---|------------|-------------|
|1  |1           |2020-03-16   |
|---|------------|-------------|
|2  |1           |2020-04-05   |
|---|------------|-------------|
|3  |2           |2020-02-05   |
|---|------------|-------------|
|4  |1           |2020-05-04   |
|---|------------|-------------|
|5  |2           |2020-03-16   |
|---|------------|-------------|

I need the count of customers where max(subscriptions.end_date) of each customer is equal to today.

My current query

select COUNT(customers.id), MAX(customer_payments.package_end_date) 
from `customers` 
inner join `customer_payments` on `customer_payments`.`customer_id` = `customers`.`id` 
group by `customer_payments`.`package_end_date` 
having DATE(customer_payments.package_end_date) = '2020-03-16'

It is returning the customers who have end_date as today also; but not of the greatest date. In the given data, if I run the query for today's date, I am getting the count 2. It is selecting both customer_id 1 and 2 even though the customer_id of 1 has the greatest date than today.

I have also tried queries mentioned in these answers also getting the same output.

Upvotes: 0

Views: 29

Answers (1)

sujivasagam
sujivasagam

Reputation: 1769

Try this

select count(id) as counted, id from subscriptions where customer_id in (select customer_id as avail_customers from subscriptions where end_date = '2020-03-16' group by customer_id) group by customer_id

Upvotes: 0

Related Questions