CODEWITHSUNDEEP
mysqlsql
lasagne
lasagne

Reputation: 641

Aggregate values under multiple conditions

Given the following tables

+-----------+-------------+----------+
|   tours   |  tour_user  | tag_tour |
+-----------+-------------+----------+
| id        | tour_id     | tag_id   |
| startdate | user_id     | tour_id  |
+-----------+-------------+----------+

I want to achieve a result set like this:

+-----------------+----------------+----------------+
| DATE(startdate) | COUNT(user_id) | COUNT(tour_id) |
+-----------------+----------------+----------------+
| 2017-12-01      |             55 |             32 |
+-----------------+----------------+----------------+

Described in words the amount of users paticipated on a tour and the amount of tours should be aggregated by days.

Additionaly the count of tour and user participation should be filterable via tags which are attachted to tours via tag_tour table (many-to-may-relation). E.g. I want only the tour and user count of tours which have tag_id 1 AND 2 attaches.

Currently I go with this Query:

SELECT DATE(tours.start) AS acum_date,
   COUNT(tour_user.user_id) AS guide_assignments,
   A.tour_count
FROM `tour_user`
LEFT JOIN `tours` ON `tours`.`id` = `tour_user`.`tour_id`
LEFT JOIN
      (SELECT DATE(tours.start) AS tour_date,
      COUNT(DISTINCT tours.id) AS tour_count
FROM tours
GROUP BY DATE(tours.start)) AS A ON `A`.`tour_date` = DATE(tours.start)
GROUP BY `acum_date`
ORDER BY `acum_date` ASC

The problem with this is, that only the total tour/user count is returned and not the filtered one.

Upvotes: 1

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

The base query is:

select t.startdate, count(tu.user_id) as num_users, count(distinct t.id) as num_tours
from tours t left join
     tour_user tu
     on tu.tour_id = t.id
group by t.startdate;

In this case, I would recommend using exists for filtering the tags:

select t.startdate, count(tu.user_id) as num_users, count(distinct t.id) as num_tours
from tours t left join
     tour_user tu
     on tu.tour_id = t.id
where exists (select 1 from tour_tags tt where tt.tour_id = t.tid and tt.tag_id = <tag1>) and
      exists (select 1 from tour_tags tt where tt.tour_id = t.tid and tt.tag_id = <tag2>)       
group by t.startdate;

Upvotes: 1

Related Questions