sum of rows

[Aditi agarwal]

i m using postgresql datbase with JSP

i need to add all the rows returned by following query

select count(*) as opy from file_reg where ((ep_flg='P')AND ((EXTRACT(year FROM open_dt))="+yr+"));

how can i do that

please help me

thankyou

[Swastik Dey]

Count(*) will return only one row, i.e total number of rows retrieved after execution of the query. So what exactly do you mean by add all the rows?

[Aditi agarwal]

thank you swastik for your reply

actually the original code is

rss=sts.executeQuery("select * from divsec where div_code=(select divcode from ams_division where divname=\'"+div.trim()+"\');");
while(rss.next())
{
no=rss.getInt("sec_code");
rsef=stef.executeQuery("select count(*) as ef  from file_reg where(sec_code="+no+") ;");
while(rsef.next())
{
}

so in this case i need all the rows returned by the 2nd query are to be sum up

thanku

please help me

[Swastik Dey]

int totalCount=0;
rss=sts.executeQuery("select * from divsec where div_code=(select divcode from ams_division where divname=\'"+div.trim()+"\');");
while(rss.next())
{
no=rss.getInt("sec_code");
rsef=stef.executeQuery("select count(*) as ef  from file_reg where(sec_code="+no+") ;");
if(rsef.next())
{
   totalCount+=rsef.getInt(1);
}

[Aditi agarwal]

thank you swastik

i also tried this but it doesnot work

[Matthew Brown]

What do you mean by "it doesn't work"? What does it do? What result do you get? If there's an error, what is it? You'll get a lot more effective help if you can give us more detail.

See ItDoesntWorkIsUseless for more details.

[Rob Spoor]

Since your database server allows nested queries, why not use one single query?

rss = sts.executeQuery("select divsec.*, (select count(*) as ef from file_reg where file_reg.sec_code = divsec.sec_code) from divsec where div_code in (select divcode from ams_division where divname = '" + div.trim() + "');");
while (rss.next())
{
    int count = rs.getInt("ef");
}

Two notes:
1) I changed from "=" to "in" in the where clause; the select statement can theoretically return multiple rows so "in" should be used. "=" should be used when selecting a single record, like a MAX, AVERAGE, etc.

2) I didn't use an INNER JOIN because then all divsec records without a matching file_reg record wouldn't show up. The following should also work:
SELECT divsec.*, COUNT(file_reg.sec_code) AS ef FROM divsec LEFT OUTER JOIN file_reg ON divsec.sec_code = file_reg.sec_code WHERE ...Just make sure to not use COUNT(*) or COUNT with any of the divsec table columns or the zeros will turn into ones.

[Aditi agarwal]

thank you Rob & Matthew for your reply

Rob this query is not working its giving error

actually my first query is returning multiple rows now i want to use those multiple rows to retrieve the data from my 2nd query but at last what is required is sum of all those rows

so what should i do now

swastik what answer you are giving is working right but again its not fulfiling my condition
its again giving multiple values to me instead of sum of all those values

please help me

[Swastik Dey]

totalCount+=rsef.getInt(1)

How come a single variable giving you multiple values?

[Rob Spoor]

Aditi agarwal wrote:Rob this query is not working its giving error

I see that I forgot to add "AS ef" just before "from divsec" in my first query. Without it the column is unnamed and you can't use named retrieval to get the value. But did you also try the second query I posted?

[Aditi agarwal]

thanks Rob & swastik for your reply

actually it was a silly bracket mistake which i had commiteed
now its running fyn