You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Bin Gao <bg...@yahoo.com> on 2006/08/08 22:10:48 UTC
Count a Subquery with group clause
Hello All,
How can I generate the following SQL statement using
OJB?
SELECT count(*) FROM (
SELECT lastname, age, city
FROM people
GROUP BY lastname, age, city
);
Thanks for your help,
Bin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Count a Subquery with group clause
Posted by Vasily Ivanov <ba...@gmail.com>.
Sorry again, :)
Your query will run, but it will not make any sence. It is the same as write:
SELECT count(*) FROM
FROM people
Cheers,
Vasily
On 8/9/06, Vasily Ivanov <ba...@gmail.com> wrote:
> Hi Bin,
>
> Your query will not run because you can not put in GROUP BY without
> count(*) in SELECT clause of subquery. If you want to run this:
> =================
> SELECT lastname, age, city, count(*)
> FROM people
> GROUP BY lastname, age, city
> =================
> that you shoud do something like this in OJB:
> =================
> ReportQueryByCriteria query =
> QueryFactory.newReportQuery(People.class, new Criteria());
> query.setAttributes(new String[] { "lastname, age, city, count(*)" });
> subQuery.addGroupBy(new String[] { "lastname, age, city" });
> broker.getReportQueryIteratorByQuery(query);
> =================
>
> Cheers,
> Vasily
>
> On 8/9/06, Bin Gao <bg...@yahoo.com> wrote:
> > Hello All,
> >
> > How can I generate the following SQL statement using
> > OJB?
> >
> > SELECT count(*) FROM (
> > SELECT lastname, age, city
> > FROM people
> > GROUP BY lastname, age, city
> > );
> >
> > Thanks for your help,
> >
> > Bin
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Count a Subquery with group clause
Posted by Bin Gao <bg...@yahoo.com>.
Thanks, Vasily.
I think your OJB code may return different results
from the SQL statement. Your OJB code will return a
number for each people group. But by the SQL statement
, we can only get a number, the count of groups.
Anyway, you gave me a great suggestion. Thanks again,
Bin
--- Vasily Ivanov <ba...@gmail.com> wrote:
> Sorry,
>
> Read this:
> subQuery.addGroupBy(new String[] { "lastname, age,
> city" });
> as this:
> query.addGroupBy(new String[] { "lastname, age,
> city" });
>
> Cheers,
> Vasily
>
> On 8/9/06, Vasily Ivanov <ba...@gmail.com> wrote:
> > Hi Bin,
> >
> > Your query will not run because you can not put in
> GROUP BY without
> > count(*) in SELECT clause of subquery. If you want
> to run this:
> > =================
> > SELECT lastname, age, city, count(*)
> > FROM people
> > GROUP BY lastname, age, city
> > =================
> > that you shoud do something like this in OJB:
> > =================
> > ReportQueryByCriteria query =
> > QueryFactory.newReportQuery(People.class, new
> Criteria());
> > query.setAttributes(new String[] { "lastname, age,
> city, count(*)" });
> > subQuery.addGroupBy(new String[] { "lastname, age,
> city" });
> > broker.getReportQueryIteratorByQuery(query);
> > =================
> >
> > Cheers,
> > Vasily
> >
> > On 8/9/06, Bin Gao <bg...@yahoo.com> wrote:
> > > Hello All,
> > >
> > > How can I generate the following SQL statement
> using
> > > OJB?
> > >
> > > SELECT count(*) FROM (
> > > SELECT lastname, age, city
> > > FROM people
> > > GROUP BY lastname, age, city
> > > );
> > >
> > > Thanks for your help,
> > >
> > > Bin
> > >
> > >
>
---------------------------------------------------------------------
> > > To unsubscribe, e-mail:
> ojb-user-unsubscribe@db.apache.org
> > > For additional commands, e-mail:
> ojb-user-help@db.apache.org
> > >
> > >
> >
>
>
---------------------------------------------------------------------
> To unsubscribe, e-mail:
> ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail:
> ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Count a Subquery with group clause
Posted by Vasily Ivanov <ba...@gmail.com>.
Sorry,
Read this:
subQuery.addGroupBy(new String[] { "lastname, age, city" });
as this:
query.addGroupBy(new String[] { "lastname, age, city" });
Cheers,
Vasily
On 8/9/06, Vasily Ivanov <ba...@gmail.com> wrote:
> Hi Bin,
>
> Your query will not run because you can not put in GROUP BY without
> count(*) in SELECT clause of subquery. If you want to run this:
> =================
> SELECT lastname, age, city, count(*)
> FROM people
> GROUP BY lastname, age, city
> =================
> that you shoud do something like this in OJB:
> =================
> ReportQueryByCriteria query =
> QueryFactory.newReportQuery(People.class, new Criteria());
> query.setAttributes(new String[] { "lastname, age, city, count(*)" });
> subQuery.addGroupBy(new String[] { "lastname, age, city" });
> broker.getReportQueryIteratorByQuery(query);
> =================
>
> Cheers,
> Vasily
>
> On 8/9/06, Bin Gao <bg...@yahoo.com> wrote:
> > Hello All,
> >
> > How can I generate the following SQL statement using
> > OJB?
> >
> > SELECT count(*) FROM (
> > SELECT lastname, age, city
> > FROM people
> > GROUP BY lastname, age, city
> > );
> >
> > Thanks for your help,
> >
> > Bin
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Count a Subquery with group clause
Posted by Vasily Ivanov <ba...@gmail.com>.
Hi Bin,
Your query will not run because you can not put in GROUP BY without
count(*) in SELECT clause of subquery. If you want to run this:
=================
SELECT lastname, age, city, count(*)
FROM people
GROUP BY lastname, age, city
=================
that you shoud do something like this in OJB:
=================
ReportQueryByCriteria query =
QueryFactory.newReportQuery(People.class, new Criteria());
query.setAttributes(new String[] { "lastname, age, city, count(*)" });
subQuery.addGroupBy(new String[] { "lastname, age, city" });
broker.getReportQueryIteratorByQuery(query);
=================
Cheers,
Vasily
On 8/9/06, Bin Gao <bg...@yahoo.com> wrote:
> Hello All,
>
> How can I generate the following SQL statement using
> OJB?
>
> SELECT count(*) FROM (
> SELECT lastname, age, city
> FROM people
> GROUP BY lastname, age, city
> );
>
> Thanks for your help,
>
> Bin
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org