Help with count query

Hi everyone hope you are all well,

I am trying to create a query from the below data which will count the unique error name + trade id , to do the count I want to only consider the error name and id fields ( this will define unique ). can this be done?data is below

tier error name Name report date File no Group name location ID
1 Error 1 Database error 15-Nov 1 Group 5 main 1
1 Error 1 Database error 15-Nov 1 Group 4 main 1
1 Error 1 Database error 15-Nov 1 Group 1 Local 1
1 Error 1 Database error 15-Nov 1 Group 3 main 1
1 Error 2 Database error 15-Nov 1 Group 1 Local 1
1 Error 2 Database error 15-Nov 1 Group 3 main 1
1 Error 2 Database error 15-Nov 1 Group 5 main 1
1 Error 3 Database error 15-Nov 1 Group 2 Local 1
1 Error 3 Database error 15-Nov 1 Group 1 Local 2
1 Error 3 Database error 15-Nov 1 Group 5 main 2
1 Error 4 Database error 15-Nov 1 Group 5 main 2
1 Error 4 Database error 15-Nov 1 Group 2 Local 2
1 Error 4 Database error 15-Nov 1 Group 1 Local 2
1 Error 5 Database error 15-Nov 1 Group 4 main 2
1 Error 5 Database error 15-Nov 1 Group 3 main 2
1 Error 6 Database error 15-Nov 1 Group 1 Local 2
1 Error 6 Database error 15-Nov 1 Group 3 main 2

The query i am trying to create should bring back the below

tes 2

I have color codes what should be group, the total count should be 7, can this be done?

thank you for any help

What you trying to count? It's not clear at all. There are 7 unique combinations of error name & ID, yet you state the count should be 5.

sorry the count should be 7 !

I've done it using a subquery that you can use just the inner part if you need to get details, or the outer part too if you only need a total count:

SELECT COUNT(*) AS total_count
FROM (
    SELECT DISTINCT [error name], ID
    FROM dbo.table_name
) AS derived

ok I need to test this out thanks for the help

how do I ad the group name in there?

If you need more columns listed, you'll need to use a separate OVER function within the SELECT, something like this:

SELECT [error name], [group name], location, ID, [count]
FROM (
    SELECT *, DENSE_RANK() OVER(ORDER BY [error name], ID) AS [count]
    FROM dbo.column_name
) AS derived
ORDER BY [count], [group name]