CODEWITHSUNDEEP
mysqljoincount
Andr
Andr

Reputation: 65

Count after joining two tables

I have the following tables:

appointment

id |    date    |   time
1  | 2018-12-02 | 10:00:00
2  | 2018-12-05 | 12:00:00
3  | 2018-12-12 | 16:00:00
4  | 2018-12-12 | 17:00:00
5  | 2018-12-13 | 09:00:00

appointment_services

id | appointment_id | service_id
1  |        1       |     24
2  |        2       |     24
3  |        3       |     21
4  |        4       |     24
5  |        5       |     18

I would like to search for a date period from appointment table and count for each service_id from appointment_services table.

So the end result will be

service_id | times
     24    |   3
     21    |   1
     18    |   1

This is what I have done so far

SELECT * FROM `appointment` a
INNER JOIN appointment_services s ON a.id = s.appointment_id
WHERE a.date BETWEEN '2018-12-10' AND '2018-12-18'

Upvotes: 1

Views: 54

Answers (5)

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/1ad0f03/1

SELECT s.service_id, COUNT(*) 
FROM `appointment_services` s
LEFT JOIN  appointment a 
ON a.id = s.appointment_id
WHERE a.date BETWEEN '2018-12-10' AND '2018-12-18'
GROUP BY s.service_id

The problem with your expected result is your WHERE clause which cut few recods, so valid result is:

service_id COUNT(*)
18          1
21          1
24          1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521028

I would recommend left joining the appointment_services table to a subquery on appointment with a restriction to the date range you want. This will allow us to retain all service values even if there be no matching appointments.

SELECT
    s.service_id,
    COUNT(a.id) AS times
FROM appointment_services s
LEFT JOIN
(
    SELECT id
    FROM appointment
    WHERE date BETWEEN '2018-12-10' AND '2018-12-18'
) a
    ON s.appointment_id = a.id
GROUP BY
    s.service_id;

enter image description here

Demo

Note that I intentionally changed the data for service_id = 18 such that its single appointment does not fall within your desired date range. Using the approach I suggest, we still report 18 with a zero count. Doing a straight inner join would have filtered off 18 entirely, and it would appear in the result set.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48770

You were close:

select s.service_id, count(*) as times
from appointment_services s
join appintment a on a.id = s.appointment_id
where a.date between '2018-12-10' and '2018-12-18'
group by s.service_id

Upvotes: 1

Maksym Fedorov
Maksym Fedorov

Reputation: 6456

You must group data by service_id column with help GROUP BY statement. For example:

SELECT s.service_id, 
       count(*) as times
FROM `appointment` a
INNER JOIN appointment_services s ON a.id = s.appointment_id
WHERE a.date BETWEEN '2018-12-10' AND '2018-12-18'
GROUP BY s.service_id

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try below - using count() aggregation and group by

 SELECT s.service_id, count(*) as cnttimes
    FROM `appointment` a
    INNER JOIN appointment_services s ON a.id = s.appointment_id
    WHERE a.date BETWEEN '2018-12-10' AND '2018-12-18'
    group by s.service_id

Upvotes: 0

Related Questions