Geek asks and answers

How to choose a record that is present more than once in the table

I have a table like this:

book_no     lang      price      shelf     
----------  --------  ---------  -----
1           eng       20         a  
4           french    34         a  
2           eng       26         b  
7           russian   71         b  
12          german    33         a  
43          french    15         d  
11          eng       43         c  
14          rusian    33         a  
19          eng       20         d  
24          french    40         c  
23          eng       57         a  
27          russian   48         b  
42          german    31         c  
25          french    15         d

from this table i want to get book_no, language, shelf.
Languages ​​that are present more than three times should only show books.
I tried:

select book_no,lang,shelf from a where (count(lang)>3)

in advance for help

+3
source share
3 answers
SELECT book_no, 
       lang, 
       shelf 
FROM   a 
WHERE  lang IN (SELECT lang 
                FROM   A 
                GROUP  BY lang 
                HAVING Count(*) > 3)
+4
source

group functions such as count, sum cannot be used in the where clause.

Use group

group by book_no -- or the fields you need, depending on sgbd, you can put one or have to put all

and availability offer

Having count(lang) > 3
+1
source

If your DBMS supports aggregate window functions, you can try the following:

SELECT
  book_no,
  lang,
  shelf
FROM (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY lang) AS book_count
  FROM a
) s
WHERE book_count >= 3
0
source

More articles:


All Articles