how to put a count or sum to all duplicates and make every unique data has a row

Question

I have this table called Table1 with fields subkey, category and frequency. I want to populate the frequency by counting per category.

Subkey	Category	Frequency
AA	apple
BB	apple
CC	banana
BB	grapes
AA	apple
AA	banana
CC	banana
BB	grapes
AA	apple

I want to achieve this output

Subkey	Category	Frequency
AA	apple	3
AA	banana	1
BB	apple	1
BB	grapes	2
CC	banana	2

My code is

 SELECT
 DISTINCT a.Category,
          a.Subkey,
      SUM(Case when a.Category    = 'apple'  then 1 
          when a.Category    = 'banana' then 1
          when a.Category    = 'grapes' then 1
 
 else 0 end) AS Frequency

 FROM Table1 as a
 Group BY Category, Subkey

I can't seem to have this output. Can you help me? My sql is based on MS SQL Server 2005 capabilities. The only supported operation is the SELECT statement.

Answer 1

Maybe i'm missing something but why not:

select Subkey, Category ,COUNT(*)
FROM Table1 
GROUP BY SubKey, Category

or if you want to update some column:

UPDATE  t
SET frequency = x.frequency
FROM    table1 t
INNER JOIN (
    SELECT  COUNT(*) AS frequency
    ,   subkey, category
    FROM    table1 t2
    GROUP BY subkey, category
    ) x
    ON  x.subkey = t.subkey
    AND x.category = t.category