how to use count function in sqlite?

Question

C:\Users\pengsir>sqlite3  e:\\test.db  
sqlite> create table test (f1 TEXT,f2 TEXT, f3 TEXT);  
sqlite> insert into test values("x1","y1","w1");  
sqlite> insert into test values("x1","y1","w2");  
sqlite> insert into test values("x1","y3","w2");  
sqlite> insert into test values("x2","y3","w2");  
sqlite> insert into test values("x3","y4","w4");   
sqlite> insert into test values("x2","y3","w4");  
sqlite> insert into test values("x1","y3","w2");  

 select f1,f2 from test where count(f2)>1 group by f1,f2;

I get the error message:Error: misuse of aggregate:,i want to get the f1 and f2 combination which the value of f1 is the same ,two or more same value of f2 to be selected,that is to say ,i want to select

x1|y1
x1|y3
x2|y3

I can do that by two sqlite commands:

sqlite> create table tmp1 as
   ...> select f1,f2,count(f2) as v from test group by f1,f2;
sqlite> select f1,f2 from tmp1 where v>1 ;

How to simplify my sqlite commands?

Have you tried select f1, f2, count(f2) cf2 from test where cf2 > 1 group by f1, f2? — Ifan Iqbal
– Ifan Iqbal, Commented Jun 8, 2014 at 8:40

CL. · Accepted Answer · 2014-06-08 09:47:27Z

2

The WHERE clause filters rows before they are aggregated by GROUP BY, so the count(f2) value is not yet available.

To filter rows after aggregation, use the HAVING clause:

SELECT f1, f2
FROM test
GROUP BY f1, f2
HAVING count(f2) > 1

answered Jun 8, 2014 at 9:47

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to use count function in sqlite?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related