GROUP BY in SQL using netbeans

Question

I'm building a web application that has a search engine. I'm using NetBeans ( Java ) and SQL databases.

I'm trying to (group by) the results of a search engine. So I don't get duplicate results and use COUNT to rank the results. I'm having a problem writing it.

Query query = em.createQuery("select s.name from Searchresult s");

this WORKS for getting the name and this one works as well for grouping the results by name

Query query = em.createQuery("select s.name from Searchresult s group by s.name");

but this one doesn't

Query query = em.createQuery("select s.name, count(s.name) from Searchresult s group by s.name");

It doesn't work whenever i'm trying to add any other field (having more than one)

Query query = em.createQuery("select s.name, s.description from Searchresult s group by s.name");

^ so this one doesn't work too

How can I write a query in that gets more than one field (name, description, url) and get the count(name) and group by the result by names?

What is the (exact) error message? And which DBMS are you using? Postgres? Oracle? — , Apr 17 '14 at 16:16
Internal Exception: java.sql.SQLSyntaxErrorException: Column reference 'DESCRIPTION' is invalid, or is part of an invalid expression. For a SELECT list with a GROUP BY, the columns and expressions being selected may only contain valid grouping expressions and valid aggregate expressions. Error Code: -1 Call: SELECT NAME, DESCRIPTION FROM SEARCHRESULT GROUP BY NAME Query: ReportQuery(referenceClass=Searchresult sql="SELECT NAME, DESCRIPTION FROM SEARCHRESULT GROUP BY NAME") — Mimo Mohasseb, Apr 17 '14 at 16:21
It gives me this error .... and i'm using netbeans ( it integrates SQL with the Java Objects ) — Mimo Mohasseb, Apr 17 '14 at 16:22
NetBeans doesn't throw any errors. It's your DBMS that does that. NetBeans has absolutely nothing to do with your problem. — , Apr 17 '14 at 16:35
Your SQL syntax is not valid. GROUP BY must follow `SELECT expression1, expression2, ... expression_n, aggregate_function (expression) FROM tables WHERE conditions GROUP BY expression1, expression2, ... expression_n;` — Sean Carroll, Apr 17 '14 at 16:51
i need to show all the table information by GROUPing them only by name .. can't i do that ? — Mimo Mohasseb, Apr 17 '14 at 17:09
possible duplicate of [select multiple columns group by one column order by count](http://stackoverflow.com/questions/12615157/select-multiple-columns-group-by-one-column-order-by-count) — Sean Carroll, Apr 17 '14 at 17:11
Thanks guys.... Apparently i was doing a silly mistake by trying to select fields that was not grouped by ... so it worked when i added s.description and s.url after the GROUP BY !... — Mimo Mohasseb, Apr 17 '14 at 17:22
Thanks SeanCarroll ! Query query = em.createQuery("select s.name, s.description, s.url from Searchresult s group by s.name, s.description, s.url order by count(s) desc"); so this query returns the search results without duplication and rank them ! — Mimo Mohasseb, Apr 17 '14 at 17:23

score 0 · Accepted Answer · edited May 23 '17 at 11:49

Duplicate of: GROUP BY without aggregate function

If you have a group by clause, only those fields in the group by and aggregate functions can be returned.

For example your second query:

select s.name, s.description from Searchresult s group by s.name

If you are collapsing all the records by s.name, what s.descriptions should the RDBMS return? There could be many s.decsription all with the same s.name.

Decide which you want back with an aggregate function:

select s.name, max(s.description) from Searchresult s group by s.name

GROUP BY in SQL using netbeans

1 Answers1