question

mrwonderful avatar image
mrwonderful asked

Looking for Count of Field1 where Field2 is in High, Medium, or Low. Field 1 can only be counted once

I need a count of how many are HIGH, MEDIUM, LOW based on the following logic Looking at the highlighted examples: L14815 counts against high but not medium L14495 counts against Medium but not low i.e. if a RECORD name is duplicated then the count only increments for the higher priority Each record name is only counted once and in its highest category only ![alt text][1] [1]: /storage/temp/3490-1.png
sqlcountdistinct
1.png (20.8 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
seanlange avatar image
seanlange answered
Pretty sparse on details here but I think something along these lines is what you are looking for. with SortedResults as ( select COUNT(*) as MyCount , RECORD_NAME , ASIT_PRIORITY , ROW_NUMBER()over(partition by RECORD_NAME order by case ASIT_PRIORITY when 'High' then 1 when 'Medium' then 2 when 'Low' then 3 end) as RowNum FROM YourTable GROUP BY RECORD_NAME , ASIT_PRIORITY ) select * from SortedResults where RowNum = 1
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.