I am trying to find the number of unique combinations in each Station by year. My data looks like this:

I need just a count of each unique combination of Track Title and Main Artist per station per year:

I am on a SQL Server using SSMS.

The closest I had gotten to the result before was

SELECT 
    Count(distinct [Track Title]) as Count,
        [Main Artist],
        [Station Code]
        [YEAR]

FROM table

GROUP BY [Main Artist], [Station Code], [Year]

And then pivoting this result in Excel to do the sums, but I was unsure of how to incorporate it into a single query.

CodePudding user response：

Remove duplicate rows in a derived table (i.e. the subquery). GROUP BY its result:

select count(*) cnt, station, year
from
(
    select distinct * from table
) dt
group by station, year

CodePudding user response：

Concatenate the 2 values and then COUNT the DISTINCT values:

SELECT COUNT(DISTINCT CONCAT(TrackTitle,'|',MainArtist)) AS [Count],
       Station,
       [Year]
FROM dbo.YourTable
GROUP BY Station,
         [Year];