Joining two seperate SQL Statement or inserting count into query

user_9327076 0
Viewed 28

Morning, I'm having trouble understand the best way to join the following two queries together.

The first code shows me the last entry by name and I'd like a count of each time the name is listed. In the second query it correctly counts the names but I can not seem to work out how to add the count into the same result.

Thanks in Advance

SELECT `TimeStamp` as TimeStamp,`Name`, `Status` ,`Station`,`Role`,`Line`
FROM trs_contact u1 
WHERE  u1.`TimeStamp` = (SELECT MAX(`TimeStamp`) 
                        FROM trs_contact u2 
                        WHERE u1.`Name` = u2.`Name`
                        ) 

SELECT `Name`, COUNT(*) 
FROM `trs_contact` 
GROUP BY `Name`
left-join join mysql
1 Answers

This query:

SELECT Name, MAX(TimeStamp) TimeStamp, COUNT(*) counter 
FROM trs_contact 
GROUP BY Name

returns both the last entry and the number of times a name exists in the table.

Join it to the table:

SELECT u1.TimeStamp, u1.Name, u1.Status, u1.Station, u1.Role, u1.Line, u2.counter
FROM trs_contact u1 
INNER JOIN (
  SELECT Name, MAX(TimeStamp) TimeStamp, COUNT(*) counter 
  FROM trs_contact 
  GROUP BY Name                      
) u2 ON u2.Name = u1.Name AND u2.TimeStamp = u1.TimeStamp

If your version of MySql is 8.0+ you can do it with ROW_NUMBER() and COUNT() window functions

SELECT TimeStamp, Name, Status, Station, Role, Line, counter
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION NY Name ORDER BY TimeStamp DESC) rn,
            COUNT(*) OVER (PARTITION NY Name) counter
  FROM trs_contact
) t
WHERE rn = 1
user_104988280
Related