Use count() operator en SQL Query

Question

I do a query in the following way:

SELECT * FROM frequency WHERE docid = '10398_txt_earn' AND count = 1
UNION
SELECT * FROM frequency WHERE docid = '925_txt_trade' AND count = 1;

With a correct output

docid   term    count
10398_txt_earn  100     1
10398_txt_earn  11340   1
10398_txt_earn  12  1
10398_txt_earn  1204    1
10398_txt_earn  198485  1
10398_txt_earn  20  1
10398_txt_earn  27  1
10398_txt_earn  28  1

And now I want to compute the number of rows in the result with the COUNT() operator but I don't know to apply it. Thanks in advance

Ubuntu14.04 SQLite with XAMPP

Gordon Linoff · Accepted Answer · 2014-07-19 01:40:01Z

1

Are you looking for this?

select count(*)
FROM (SELECT * FROM frequency WHERE docid = '10398_txt_earn' AND count = 1
      UNION
      SELECT * FROM frequency WHERE docid = '925_txt_trade' AND count = 1
     ) t;

By the way, you can write your original query more efficiently as:

SELECT *
FROM frequency
WHERE count = 1 and (docid in  '10398_txt_earn', '925_txt_trade');

The count is then:

SELECT count(*)
FROM frequency
WHERE count = 1 and (docid in  '10398_txt_earn', '925_txt_trade');

answered Jul 19, 2014 at 1:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Gordon Linoff Over a year ago

The t is a table alias. This is needed for subqueries in MySQL and necessary for certain join operations. In general, though, table aliases are useful to make queries easier to write and read.

Juan David Over a year ago

Is something like a fake table or a temp table right?

Gordon Linoff Over a year ago

@jd1215 . . . It is just a name for a table or subquery.

Michael Kang · Accepted Answer · 2014-07-19 01:40:11Z

1

You can do:

SELECT COUNT(1)
FROM 
(
  SELECT * FROM frequency WHERE docid = '10398_txt_earn' AND count = 1
  UNION
  SELECT * FROM frequency WHERE docid = '925_txt_trade' AND count = 1
) a

Since you used a 'UNION', your results will remove duplicates. If you want to include all records (including any duplicates), then use a UNION ALL instead of a UNION.

answered Jul 19, 2014 at 1:40

Michael Kang

53k16 gold badges108 silver badges138 bronze badges

Collectives™ on Stack Overflow

Use count() operator en SQL Query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related