JT2013 JT2013 - 1 month ago 11
SQL Question

Counting unique values in rows and columns in MS Access query

I currently have a table that looks like the following:

[BUSINESS_PROCESS_ID] [Error Type 1] [Error Type 2] [Error Type 3]



PVT02 108 93
PVT02 98
PVT02 80 80 80
PVT02 80
PVT02 93
PVT02 27 80
PVT03 21
PVT03 102
PVT03 80
PVT03 80 102
PVT03 80
PVT03 80
PVT03 71



Based on each Business_Process_Id, i'm looking to write a query that counts each unique error type. Ie. the query output should look like the following:

[BUSINESS_PROCESS_ID] [Error Type] [Count of Unique Error Types Combined]



PVT02 108 1
PVT02 98 1
PVT02 80 5
PVT02 93 2
PVT02 27 1
PVT02 98 1
PVT03 21 1
PVT03 102 2
PVT03 80 4
PVT03 71 1



As you can see, I'm not concerned with whether or not the count is by Error Type 1, 2 or 3. I'm looking to get the count of UNIQUE Error Types (ie. 98,80,108 etc.) by Business_Process_ID.

Could someone help? Thank You.

Answer

One way to accomplish this would be to use a UNION query to first put all error types into a single column. Then, you can use a simple query to aggregate the results into how you wish to see them.

SELECT BUSINESS_PROCESS_ID, [Error Type 1] as ErrorType
FROM TableName
UNION ALL
SELECT BUSINESS_PROCESS_ID, [Error Type 2] as ErrorType
FROM TableName
UNION ALL
SELECT BUSINESS_PROCESS_ID, [Error Type 3] as ErrorType
FROM TableName

Save this as a query.

Then, use this query in a aggregation query similar to the following:

SELECT BUSINESS_PROCESS_ID, ErrorType, Count(ErrorType) as Number_Of_Errors
FROM MyUnionQuery
GROUP BY BUSINESS_PROCESS_ID, ErrorType
Comments