How to write this SQL query using having clause?

Question

My database schema is like following

table - X has following 3 columns docid(document id), terms(terms in document), count(number of terms of occurence of the term for specific docid)

docid
terms
count

How to extract the information as for the following The number of docid where sum of total words is more than 300 including duplicate words.

Mahmoud Gamal · Answer 1 · 2013-05-16T07:49:33.087

5

Like this:

SELECT term, SUM(count) AS Totalwords
FROM tablex
GROUP BY  term
HAVING SUM(count) > 300

Update: Try this:

SELECT docid, SUM(count)
FROM tablex
GROUP BY docid
HAVING SUM(count) > 300;

See it in action here:

SQL Fiddle Demo.

edited May 16 '13 at 07:49

answered May 16 '13 at 07:22

Mahmoud Gamal

78,257
17
139
164

we don't need to take care of docid? because ultimately we have to find the total documents which has more than 300 words – Surjya Narayana Padhi May 16 '13 at 07:34
@SurjyaNarayanaPadhi - Isn't the docid is a primary key in that table? What should be the grouped by column? is it `docid`? can you please show some sample data and the desired output? – Mahmoud Gamal May 16 '13 at 07:37
the schema is like this - CREATE TABLE Frequency ( docid VARCHAR(255), term VARCHAR(255), count int, PRIMARY KEY(docid, term)); – Surjya Narayana Padhi May 16 '13 at 07:38
the schema is like this - CREATE TABLE Frequency ( docid VARCHAR(255), term VARCHAR(255), count int, PRIMARY KEY(docid, term)); – Surjya Narayana Padhi May 16 '13 at 07:39
sample data - 9988_txt_interest|funds|1 9988_txt_interest|6316|1 9988_txt_interest|york|2 9988_txt_interest|said|1 9988_txt_interest|sets|1 9988_txt_interest|time|1 – Surjya Narayana Padhi May 16 '13 at 07:41
desired output will be tuples of docid and count(which is total words in this document) – Surjya Narayana Padhi May 16 '13 at 07:43
@SurjyaNarayanaPadhi See my update. It should work fine. Try it and let me know. – Mahmoud Gamal May 16 '13 at 07:49
thanks a lot. It worked. Just little bit more help i need. How can i get the count of the rows printed in the query result. – Surjya Narayana Padhi May 16 '13 at 08:02
@SurjyaNarayanaPadhi What RDBMS you are using? and what are you using to execute the query? do you execute it from a fron end application like php or c# or directly in the database management studio? – Mahmoud Gamal May 16 '13 at 08:07
I am using sqlite3. I am sending the queries from PHP. – Surjya Narayana Padhi May 16 '13 at 08:24
@SurjyaNarayanaPadhi - See [**this post**](http://stackoverflow.com/a/2586771/722783), it might be helpful. – Mahmoud Gamal May 16 '13 at 08:38

score 1 · Answer 2 · answered May 16 '13 at 07:42

1

You can use

SELECT docid,SUM(COUNT) AS CNT
FROM X
GROUP BY docid
HAVING SUM(count) > 300

answered May 16 '13 at 07:42

Rohan

3,068
1
20
26

How to write this SQL query using having clause?

2 Answers2