JimmyJimm JimmyJimm - 5 months ago 6
SQL Question

Get highest count for specific rows

I would like to return highest count of keywords which belongs to specific kategorie, subkategorie and type.

We have to use

WHERE SubKat_ID IS NOT NULL and only WHERE Type = A


So something like this:


SELECT 'get highest count of keywords' from table WHERE Kat_ID = x And
Type = 'A' And SubKat IS NOT NULL


Example data:

ID Keyword_ID Kat_ID SubKat_ID Type
29 1 247 NULL A
30 2 247 NULL A
31 3 247 NULL A
32 3 247 96 A
33 4 247 96 A
34 2 247 96 A
35 3 247 95 A
37 4 33 NULL B
40 6 33 44 A
41 3 33 44 A
42 4 33 66 A
43 11 33 66 A
44 7 33 66 A
45 2 33 66 A
46 8 55 NULL A


Couple examples based on data at the bottom of my post:

1st Test:

WHERE Kat_ID = 247 And Type = 'A'


it should says: 3 because we have: 3x rows for 96 and 1x95

2nd Test:

WHERE Kat_ID = 33 And Type = 'A'


it should says: 4 because we have: 2x rows for 44 and 4x66

3rd Test:

WHERE Kat_ID = 55 And Type = 'A'


it should says: 0

Answer

This should deal with your edge case too:

select coalesce(max(t.cnt), 0) as max_cnt
  from (select count(*) as cnt
          from tbl
         where kat_id = x
           and type = 'A'
           and SubKat_ID is not null
         group by SubKat_ID) t
Comments