CODEWITHSUNDEEP
sqlsql-server
user6156963
user6156963

Reputation:

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

Upvotes: 5

Views: 72

Answers (5)

RDJ
RDJ

Reputation: 191

Try this:

SELECT TOP 1  CASE WHEN RecType = 'NULL' THEN 0 ELSE cnt END AS RecordCount FROM--TOP 1 FROM 
(
    SELECT COUNT(*) as cnt, 'NOT NULL' as RecType
    FROM #tmp
    WHERE Kat_ID = 33 AND Type = 'A' 
        AND SubKat_ID is not null
    GROUP BY SubKat_ID
UNION
    SELECT COUNT(*) AS cnt, 'NULL' AS RecType 
    FROM #tmp WHERE Kat_ID = 33 AND Type = 'A' 
        AND SubKat_ID IS NULL
) AS T1
ORDER BY cnt desc

But I feel I'm missing a simpler way.

Upvotes: 0

MNC
MNC

Reputation: 21

select  max(count(1)) from stck
WHERE Kat_ID = 247 And Type = 'A' and SubKat_ID  is not null
group by SubKat_ID 

select  max(count(1)) from stck
WHERE Kat_ID = 33 And Type = 'A' and SubKat_ID  is not null
group by SubKat_ID 

select  nvl(max(count(1)),0)  from stck
WHERE Kat_ID = 55 And Type = 'A' and SubKat_ID  is not null
group by SubKat_ID 

Have written above queries on top oracle db

Upvotes: 0

sstan
sstan

Reputation: 36473

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

Upvotes: 2

Max Sorin
Max Sorin

Reputation: 1052

SELECT count(1) as [Count], Kat_ID, SubKat_ID, [Type]

from table 

WHERE Kat_ID = x And Type = 'A' And SubKat IS NOT NULL

group by Kat_ID, SubKat_ID, [Type]

You want to use the group by clause to tell the engine to group equal values in these column together.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Is this what you want?

SELECT TOP 1 COUNT(*)
FROM T
WHERE Kat_ID = 247 And Type = 'A' 
GROUP BY SubKat_ID
ORDER BY COUNT(*) DESC;

Upvotes: 1

Related Questions