Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sum of rows

 
Aditi agarwal
Ranch Hand
Posts: 225
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1604
5
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 225
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1604
5
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Aditi agarwal
Ranch Hand
Posts: 225
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you swastik

i also tried this but it doesnot work
 
Matthew Brown
Bartender
Posts: 4567
8
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20550
57
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Aditi agarwal
Ranch Hand
Posts: 225
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1604
5
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


How come a single variable giving you multiple values?
 
Rob Spoor
Sheriff
Pie
Posts: 20550
57
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 225
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks Rob & swastik for your reply

actually it was a silly bracket mistake which i had commiteed
now its running fyn
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic