Untitled

SQL calculate item frequency using multiple / dependent columns?
state     fruit     popularity

hawaii    apple
hawaii    apple
hawaii    banana
hawaii    kiwi
hawaii    kiwi
hawaii    mango
florida   apple
florida   apple
florida   apple
florida   orange
michigan  apple
michigan  apple
michigan  apricot
michigan  orange
michigan  pear
michigan  pear
michigan  pear
texas     apple
texas     banana
texas     banana
texas     banana
texas     grape

state     fruit     popularity

hawaii    apple     4
hawaii    apple     4
hawaii    banana    2
hawaii    kiwi      1
hawaii    kiwi      1
hawaii    mango     1
florida   apple     4
florida   apple     4
florida   apple     4
florida   orange    2
michigan  apple     4
michigan  apple     4
michigan  apricot   1
michigan  orange    2
michigan  pear      1
michigan  pear      1
michigan  pear      1
texas     apple     4
texas     banana    2
texas     banana    2
texas     banana    2
texas     grape     1

--outputs those fruits appearing multiple times in the table
SELECT fruit, COUNT(*)
  FROM table
 GROUP BY fruit
HAVING COUNT(*) > 1
 ORDER BY COUNT(*) DESC

--outputs those fruits appearing only once in the table
SELECT fruit, COUNT(*)
  FROM table
 GROUP BY fruit
HAVING COUNT(*) = 1

--outputs list of unique fruits in the table
SELECT COUNT (DISTINCT(fruit))
  FROM table

update my_table x
   set popularity = ( select count(distinct state)
                        from my_table
                       where fruit = x.fruit )

select state, fruit
     , count(distinct state) over ( partition by fruit ) as popularity
  from my_table

select fruit, COUNT(distinct state) statecount from #fruit group by fruit

update #fruit
set popularity
    = statecount
from
 #fruit
    inner join
      (select fruit, COUNT(distinct state) statecount from #fruit group by fruit) sc
        on #fruit.fruit = sc.fruit

update table
set count = cnt
from
  (
    select fruit, count(distinct state) as cnt
    from table
    group by fruit) cnts
  inner join table t
    on cnts.fruit = t.fruit

SELECT fruit
,      COUNT(*)
FROM
(
SELECT state
,      fruit
,      ROW_NUMBER() OVER (PARTITION BY state, fruit ORDER BY NULL) rn
FROM   t
)
WHERE rn = 1
GROUP BY fruit
ORDER BY fruit;

select a.*,b.total
from [table] as a
left join
(
SELECT fruit,count(distinct [state]) as total
  FROM [table]
  group by fruit
) as b
on a.fruit = b.fruit

WITH t
  AS (SELECT 'hawaii' as STATE, 'apple' as fruit FROM dual
      UNION ALL
      SELECT 'hawaii' as STATE, 'apple' as fruit FROM dual
      UNION ALL
      SELECT 'hawaii' as STATE, 'banana' as fruit FROM dual
      UNION ALL
      SELECT 'hawaii' as STATE, 'kiwi' as fruit FROM dual
      UNION ALL
      SELECT 'hawaii' as STATE, 'kiwi' as fruit FROM dual
      UNION ALL
      SELECT 'hawaii' as STATE, 'mango' as fruit FROM dual
      UNION ALL
      SELECT 'florida' as STATE, 'apple' as fruit FROM dual
      UNION ALL
      SELECT 'florida' as STATE, 'apple' as fruit FROM dual
      UNION ALL
      SELECT 'florida' as STATE, 'apple' as fruit FROM dual
      UNION ALL
      SELECT 'florida' as STATE, 'orange' as fruit FROM dual
      UNION ALL
      SELECT 'michigan' as STATE, 'apple' as fruit FROM dual
      UNION ALL
      SELECT 'michigan' as STATE, 'apple' as fruit FROM dual
      UNION ALL
      SELECT 'michigan' as STATE, 'apricot' as fruit FROM dual
      UNION ALL
      SELECT 'michigan' as STATE, 'orange' as fruit FROM dual
      UNION ALL
      SELECT 'michigan' as STATE, 'pear' as fruit FROM dual
      UNION ALL
      SELECT 'michigan' as STATE, 'pear' as fruit FROM dual
      UNION ALL
      SELECT 'michigan' as STATE, 'pear' as fruit FROM dual
      UNION ALL
      SELECT 'texas' as STATE, 'apple' as fruit FROM dual
      UNION ALL
      SELECT 'texas' as STATE, 'banana' as fruit FROM dual
      UNION ALL
      SELECT 'texas' as STATE, 'banana' as fruit FROM dual
      UNION ALL
      SELECT 'texas' as STATE, 'banana' as fruit FROM dual
      UNION ALL
      SELECT 'texas' as STATE, 'grape' as fruit FROM dual)
SELECT state,
       fruit,
       count(DISTINCT state) OVER (PARTITION BY fruit) AS popularity
  FROM t;

florida     apple   4
florida     apple   4
florida     apple   4
hawaii      apple   4
hawaii      apple   4
michigan    apple   4
michigan    apple   4
texas       apple   4
michigan    apricot 1
hawaii      banana  2
texas       banana  2
texas       banana  2
texas       banana  2
texas       grape   1
hawaii      kiwi    1
hawaii      kiwi    1
hawaii      mango   1
florida     orange  2
michigan    orange  2
michigan    pear    1
michigan    pear    1

SELECT state,
       fruit,
       count(DISTINCT state) OVER (PARTITION BY fruit) AS popularity
  FROM table_name;

create table states([state] varchar(10),fruit varchar(10),popularity int)
INSERT INTO states([state],fruit)
VALUES('hawaii','apple'),
('hawaii','apple'),
('hawaii','banana'),
('hawaii','kiwi'),
('hawaii','kiwi'),
('hawaii','mango'),
('florida','apple'),
('florida','apple'),
('florida','apple'),
('florida','orange'),
('michigan','apple'),
('michigan','apple'),
('michigan','apricot'),
('michigan','orange'),
('michigan','pear'),
('michigan','pear'),
('michigan','pear'),
('texas','apple'),
('texas','banana'),
('texas','banana'),
('texas','banana'),
('texas','grape')

update t set t.popularity=a.cnt
from states t inner join
(SELECT fruit,count(distinct [state]) as cnt
  FROM states
  group by fruit) a
on t.fruit =a.fruit