SQLTeam.com | Weblogs | Forums

Help with count query


#1

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


#2

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.


#3

sorry the count should be 7 !


#4

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

#5

ok I need to test this out thanks for the help


#6

how do I ad the group name in there?


#7

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]