CODEWITHSUNDEEP
sqlsql-servert-sql
Emil Visti
Emil Visti

Reputation: 55

Count unique values and then also count instances of those unique values with criteria?

I am wondering if it is possible in SQL Server (through SSMS) to count unique values in column [Track-ID]. Then I would like to count how many of these unique values fulfil criteria in other columns:

Having 1 in [Points Assigned]
Having anything but 0 in [Playing Time Sequence no_]

I would like to count both things independently, but only on the unique [Track-ID] values.

Is this possible? I have attempted to do it using the query below:

SELECT      
    MONTH([Date of Broadcasting]) AS Month
    , COUNT(DISTINCT [Track-ID]) AS Total_unikke
    , COUNT(DISTINCT(CASE WHEN [Points Assigned] = 1 THEN 1 ELSE 0 END)) AS Points
    , COUNT(DISTINCT(CASE WHEN [Playing Time Sequence no_] != 0 THEN 1 ELSE 0 end)) AS PlayingTimeSequence
FROM [tblGT$Programme Line]
WHERE YEAR([Date of Broadcasting]) = 2020
GROUP BY MONTH([Date of Broadcasting])

Upvotes: 0

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Yes . . . you are close but the logic looks like this:

SELECT MONTH([Date of Broadcasting]) as Month
       COUNT(Distinct [Track-ID]) as Total_unikke,
       count(distinct case when [Points Assigned] = 1 then [Track-ID] end) as Points
       count(distinct case when [Playing Time Sequence no_] <> 0 then [Track-ID] end) as PlayingTimeSequence
FROM [tblGT$Programme Line]
WHERE YEAR([Date of Broadcasting]) = 2020
GROUP BY MONTH([Date of Broadcasting]);

That is, the CASE expression needs to return the column you want to count distinctly.

Upvotes: 1

Related Questions