Geek asks and answers

Does SQL calculate element frequency using multiple columns / dependent columns?

I am completely new to SQL and reading StackOverflow messages in SQL to try to figure this out and other sources and could not do it in SQL. Here goes ...

I have a table of 3 columns and thousands of rows with data for the first 2 columns. The third column is currently empty, and I need to populate the third column based on the data already in the first and second columns.

Let's say that I have states in the first column and fruit records in the second column. I need to write an SQL statement (s) that calculates the number of different states in which each fetus comes from , and then inserts this popularity number in the third column for each row. The popularity number 1 on this line means that the fruit comes from only one state, the popularity number 4 means that the fruit comes from 4 states. So my table is currently similar:

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

, , , , . , ( ) , "" 4 , , , , 1 , , .

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

- , - script, SQL , , SQL. , ? / SQL.

- , (-) SQL , , ? , .

SQL- , , , :

--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
+5
7

, :

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

.

+3

( ) , :

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;

, ...

+1

#fruit...

To calculate different conditions for each fruit

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

and thus update the table with these values

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
0
source

That should make you most of the way. Basically, you want to get the number of different states the fetus is in, and then use them to connect to the source table.

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
0
source

Another option:

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;
0
source

Try the following:

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

Please note that this is SQL Server code; if necessary, make your own settings.

0
source

try it

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
0
source

More articles:


All Articles