Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes sum of rows Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sum of rows" Watch "sum of rows" New topic
Author

sum of rows

Aditi agarwal
Ranch Hand

Joined: Feb 23, 2011
Posts: 225
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
Rancher

Joined: Jan 08, 2009
Posts: 1478
    
    6

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?


Swastik
Aditi agarwal
Ranch Hand

Joined: Feb 23, 2011
Posts: 225
thank you swastik for your reply

actually the original code is



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
Rancher

Joined: Jan 08, 2009
Posts: 1478
    
    6

Aditi agarwal
Ranch Hand

Joined: Feb 23, 2011
Posts: 225
thank you swastik

i also tried this but it doesnot work
Matthew Brown
Bartender

Joined: Apr 06, 2010
Posts: 4467
    
    8

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
Sheriff

Joined: Oct 27, 2005
Posts: 19762
    
  20

Since your database server allows nested queries, why not use one single query?
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:
Just make sure to not use COUNT(*) or COUNT with any of the divsec table columns or the zeros will turn into ones.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Aditi agarwal
Ranch Hand

Joined: Feb 23, 2011
Posts: 225
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
Rancher

Joined: Jan 08, 2009
Posts: 1478
    
    6



How come a single variable giving you multiple values?
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19762
    
  20

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
Ranch Hand

Joined: Feb 23, 2011
Posts: 225
thanks Rob & swastik for your reply

actually it was a silly bracket mistake which i had commiteed
now its running fyn
 
 
subject: sum of rows