Question 20071 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL count as column – Mysql

I have two tables, Table_One & Table_two. I have written an SQL statement that Joins Table_One to the latest record of Table_two. What I want is to include the read_reciept(count) as a column in the result.

Table_One:

ID Name
1 John
2 Tom
3 Anna

Table_two:

ID Visit ID Date read_reciept
1 2513 5/5/2001 1
1 84654 10/5/2012 1
1 454 4/20/2018 1
2 754 4/5/1999 0
2 654 8/8/2010 1
2 624 4/9/1982 1
3 7546 7/3/1997 0
3 246574 6/4/2015 1
3 15487 3/4/2017 1

This is the code I adopted:

SELECT ID, Name, VisitID, Date
FROM (
    SELECT t1.ID, t1.Name, t2.VisitID, t2.Date,
        row_number() over (PARTITION BY t1.ID ORDER BY t2.Date DESC) rn
    FROM Table_One t1
    INNER JOIN Table_Two t2 ON t2.ID = t1.ID
) t 
WHERE rn = 1

The result of the code:

ID Name Visit ID Date
1 John 454 4/20/2018
2 Tom 654 8/8/2010
3 Anna 246574 6/4/2015

Results needed:

ID Name Visit ID Date read_reciept
1 John 454 4/20/2018 3
2 Tom 654 8/8/2010 2
3 Anna 246574 6/4/2015 2
Tags:

3

Answers


  1. 0 
    SELECT ID, Name, VisitID, Date, sum_read_receipt
FROM (
    SELECT t1.ID, t1.Name, t2.VisitID, t2.Date,
        row_number() over (PARTITION BY t1.ID ORDER BY t2.Date DESC) rn,
        sum(read_receipt) over (PARTITION BY t2.ID) AS sum_read_receipt
    FROM Table_One t1
    INNER JOIN Table_Two t2 ON t2.ID = t1.ID
) t
WHERE rn = 1;

    Result given your data:

    +----+------+---------+------------+------------------+
| ID | Name | VisitID | Date       | sum_read_receipt |
+----+------+---------+------------+------------------+
|  1 | John |     454 | 2018-04-20 |                3 |
|  2 | Tom  |     654 | 2010-08-08 |                2 |
|  3 | Anna |   15487 | 2017-03-04 |                2 |
+----+------+---------+------------+------------------+

    DbFiddle demo

    Tip: Use YYYY-MM-DD for MySQL date format.

    Login or Signup to reply.
  2. 0

    This can be done using count() with case when clause :

    SELECT ID, Name, VisitID, Date, read_reciept
FROM (
    SELECT t1.ID, t1.Name, t2.VisitID, t2.Date,
        row_number() over (PARTITION BY t1.ID ORDER BY t2.Date DESC) rn,
        count(case when read_receipt = 1 then 1 end) over (PARTITION BY t2.ID) AS read_reciept
    FROM Table_One t1
    INNER JOIN Table_Two t2 ON t2.ID = t1.ID
) t 
WHERE rn = 1

    Demo here

    Login or Signup to reply.
  3. 0

    Based on @BillKarwin’s answer, I tried the equivalent of MSSQL’s OUTER APPLY in MySQL:

    SELECT t1.ID, t1.Name, t2a.VisitID, t2a.Date, t2b.sum_read_receipt
FROM Table_One t1
INNER JOIN (
    SELECT ID, SUM(read_receipt) AS sum_read_receipt 
    FROM Table_Two GROUP BY ID
) t2b ON t2b.ID = t1.ID
JOIN LATERAL (
    SELECT VisitID, Date FROM Table_Two t2a 
    WHERE t2a.ID = t1.ID ORDER BY Date DESC LIMIT 1
) t2a ON 1=1

    DbFiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search