MySQL: Create view with "inner join" and "count" on grouped data-CodePudding

My database contains a single table with lots of different data columns. Following simple representation shows the columns I am currently interested in:

Table Playlist:

id       metadata                   lastplayed
===============================================
1        All Night                  1571324631
2        Perfect Replacement        1571324767
3        One More Day               1571324952
4        Stay Awake                 1571325184
5        Perfect Replacement        1571325386
6        All Night                  1571325771
7        Close Enemies              1571326422

I already have a View which groups the metadata, so I can see all single occurrences of the songs and when they were last played (epoch seconds).

View 'Music' (desired result):

id       metadata                   lastplayed      count (*)
==============================================================
3        One More Day               1571324952      1
4        Stay Awake                 1571325184      1
5        Perfect Replacement        1571325386      2
6        All Night                  1571325771      2
7        Close Enemies              1571326422      1

The column "count" does not yet exist in the View, and I would like to include it via the existing SQL script that creates the View:

CREATE VIEW `Music` AS
SELECT 
  t1.`id`,
  t1.`metadata`,
  t1.`lastplayed`
FROM Playlist t1
INNER JOIN
(
    SELECT `metadata`, MAX(`lastplayed`) AS `timestamp`
    FROM Playlist
    GROUP BY `metadata`
) t2
    ON t1.`metadata` = t2.`metadata` AND t1.`lastplayed` = t2.`timestamp`
ORDER BY t1.`Id` ASC

So now I am running into the problem where and how to put my COUNT(metadata) AS count line, to get the desired result. When I add it in the top SELECT row, the table is reduced to a single data line with one song and the count of all data rows.

CodePudding user response：

Put it in the inner select

CREATE VIEW `Music` AS
SELECT 
  t1.`id`,
  t1.`metadata`,
  t2.`lastplayed`,
  t2.count
FROM Playlist t1
INNER JOIN
(
    SELECT `metadata`, MAX(`lastplayed`) AS `timestamp`, COUNT(*) AS count
    FROM Playlist
    GROUP BY `metadata`
) t2
    ON t1.`metadata` = t2.`metadata` AND t1.`lastplayed` = t2.`timestamp`
ORDER BY t1.`Id` ASC

CodePudding user response：

You just need a simple aggregation through grouping by metadata column

CREATE OR REPLACE VIEW `Music` AS
SELECT MAX(id) AS id, `metadata`, MAX(`lastplayed`) AS lastplayed, COUNT(*) AS count 
  FROM Playlist
 GROUP BY `metadata`