CODEWITHSUNDEEP
jpa
uncle bob
uncle bob

Reputation: 680

How to run an aggregate function like SUM on two columns in JPA and Distinct?

Now i'm using JPA of Hibernate 4.0.1, i have two tables with relationship ManyToOne (Company has many contract).

I have requirement to sum all value of contracts which belong to a company and has a specific state (RUNNING/CLOSED/...).

My Sql like this:

SELECT SUM(contract.totalAmount) FROM Contract contract, CompanyEntity company
WHERE contract.state LIKE :contractState AND contract.company.id = :companyId"

But it return a duplicated total somehow. I read this post when i write SUM query.

So my question, can we make a aggregate function like SUM with DISTINCT in JPA NamedQuery?

Thanks all,

Upvotes: 0

Views: 1300

Answers (1)

grizzly
grizzly

Reputation: 608

Yes, SUM(distinct contract.totalAmount) in your query would result in distint count. And as JB Nizet mentioned, the reason for duplicate records is because of the cross join with companyEntity. The correct way would be to explicitly use join and then apply filter on company id.

SELECT SUM(contract.totalAmount) FROM Contract contract Join contract.company cpy
WHERE contract.state LIKE :contractState AND cpy.id = :companyId

(Assuming company is the variable name in contract class with JoinTable annotation)

Upvotes: 1

Related Questions