Count from multiple tables with group

Question

I have two tables. The first tables registers admissions. The second one has exits, as shown below:

 Table 1: Admissions
+----------+---------------------+---------+
| entry_id | join_date           | name    |
+----------+---------------------+---------+
|       26 | 2017-01-01 00:00:00 | James   |
|       29 | 2017-01-01 00:00:00 | Jan     |
|       27 | 2017-01-01 00:00:00 | Chris   |
|       28 | 2017-01-01 00:00:00 | Mary    |
|       22 | 2017-01-02 00:00:00 | Anna    |
|       21 | 2017-01-02 00:00:00 | Andy    |
|       24 | 2017-01-02 00:00:00 | Bob     |
|       20 | 2017-01-04 00:00:00 | Alice   |
|       23 | 2017-01-04 00:00:00 | Chris   |
|       25 | 2017-01-04 00:00:00 | Happy   |
+----------+---------------------+---------+

Table 2: Exits
+----------+---------------------+----------+
| entry_id | exit_date           | name     |
+----------+---------------------+----------+
|      322 | 2017-01-01 00:00:00 | Kay      |
|      344 | 2017-01-01 00:00:00 | Agnes    |
|      920 | 2017-01-02 00:00:00 | Andre    |
|      728 | 2017-01-02 00:00:00 | Mark     |
|      583 | 2017-01-03 00:00:00 | Alsta    |
|      726 | 2017-01-03 00:00:00 | Bull     |
|      816 | 2017-01-03 00:00:00 | Jane     |
|      274 | 2017-01-04 00:00:00 | Jack     |
|      723 | 2017-01-04 00:00:00 | Anna     |
|      716 | 2017-01-04 00:00:00 | Bill     |
+----------+---------------------+----------+

I am looking for a solution to know the number of admissions, the number of exits and the balance, grouped by date.

I am looking for this >

+---------------------+--------+--------+-----------+
| date                | joins  | exist  | net       |
+---------------------+--------+--------+-----------+
| 2017-01-01 00:00:00 |      4 |      2 |         2 |
| 2017-01-02 00:00:00 |      3 |      2 |         1 |
| 2017-01-03 00:00:00 |      0 |      3 |        -3 |
| 2017-01-04 00:00:00 |      3 |      3 |         0 |
+---------------------+--------+--------+-----------+

Notes: There may be days when admissions occur, but no exits are registered and vice versa.

Caius Jard · Answer 1 · 2018-12-06T11:14:32.933

Here you go:

SELECT
  d,
  SUM(CASE WHEN t = 'j' THEN 1 ELSE 0 END) as joins,
  SUM(CASE WHEN t = 'x' THEN 1 ELSE 0 END) as exits,
  SUM(CASE WHEN t = 'j' THEN 1 ELSE 0 END) - SUM(CASE WHEN t = 'x' THEN 1 ELSE 0 END) as net
FROM
  (SELECT join_date as d, 'j' as t FROM admissions) j
  UNION ALL
  (SELECT exit_date as d, 'x' as t FROM exits) x
GROUP BY d

We concatenate the data using a UNION ALL and make a note of its type- join or exit, with a simple char we can compare later

We group this up by d, giving one date per line, and we sum the result of conditionally looking at whether its a 'j'oin or an e'x'it. If the line is a j, then a 1 is added to the column tracking the joins total for that day, and so on

The only thing this doesn't give you, is days where there are no joins or exits.. (For example 2018-12-25, 0, 0, 0 because xmas day was closed and noone did anything on that day).. But you didn't say you wanted those.

If you do want lines with a date, and 0 exits, 0 joins, 0 net, then we have to we have to work some additional magic, and it's a bit more of a headache/makes it harder to understand (so i left it out)

score 1 · Answer 2 · answered Dec 06 '18 at 11:13

Following will do:

    select 
        CASE WHEN join_date is not null THEN join_date 
             WHEN exit_date is not null THEN exit_date END as date,
        entry.cnt as joins,
        exit.cnt as exits,
        (extry.cnt - exit.cnt) as net
    FROM
        (select join_date, COALESCE(count(*), 0) as cnt from Admissions group by join_date) entry 
    FULL OUTER JOIN
        (select exit_date, COALESCE(count(*), 0) as cnt from Exits group by exit_date) exit 
    ON 
        entry.join_date=exit.exit_date
    ;

Some small tips for you, Agrawal: The `SELECT CASE WHEN` can be more neatly written as `SELECT COALESCE(join_date, exit_date)`. There's no need to COALESCE the count - it can never return a null — Caius Jard, Dec 06 '18 at 16:21

exudong · Accepted Answer · 2018-12-06T12:48:37.727

0

I didn't find the answer. Here's an answer from one of my friends, below is a MySQL version:

select aa.date, IFNULL(aa.joins, 0) joins, IFNULL(bb.exits,0) exits, (IFNULL(aa.joins,0) - IFNULL(bb.exits,0)) net
from
(
  select join_date date, count(name) joins
  from Admissions
  group by join_date
) aa
left join
(
 select exit_date date, count(name) exits
 from Exits
 group by exit_date
) bb on aa.date = bb.date

UNION

select bb.date, IFNULL(aa.joins, 0) joins, IFNULL(bb.exits,0) exits, (IFNULL(aa.joins,0) - IFNULL(bb.exits,0)) net
from
(
  select join_date date, count(name) joins
  from Admissions
  group by join_date
) aa
right join
(
 select exit_date date, count(name) exits
 from Exits
 group by exit_date
) bb on aa.date = bb.date order by date;

edited Dec 06 '18 at 12:48

answered Dec 06 '18 at 12:33

exudong

366
3
13

Your friends need to read up on what a FULL OUTER JOIN is. They should also put some effort into improving their performance considerations; this query does about 3 times more work than it needs to – Caius Jard Dec 06 '18 at 16:16
@CaiusJard, I tried to optimize as your comments, but mysql does not support FULL OUTER JOIN, and the work around is like this in mysql, https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql. so the solution is specific to mysql. – exudong Dec 08 '18 at 08:05
Beg your pardon; i'd forgotten mysql still lacks FOJ support – Caius Jard Dec 08 '18 at 12:44

Count from multiple tables with group

3 Answers3