Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

count(distinct field1 || field2)

 
Bianca Bromonti
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Is there a solution for select count(distinct field1 || field2)?
It gives org.hibernate.hql.ast.QuerySyntaxException: expecting CLOSE, found '||' near line 2, column 33 exception.

Thanks in advance:
Bianca
 
Dave Tolls
Ranch Hand
Posts: 2099
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What are you trying to achieve with your query?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Bianca Bromonti,

First of all, a warm welcome to CodeRanch!

Bianca Bromonti wrote:Is there a solution for select count(distinct field1 || field2)?

Can you tell us what you are actually trying to achieve with your query (as Dave already suggested)? And if it's a bit complex or hard to explain, you could add some sample data of the input and the expected output.

Kind regards,
Roel
 
Bianca Bromonti
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Roel,

Thank you for your answer.

I have some reports in iReport where I use HQL.
The report has subreports (composition labels) and I have to choose if I show the slim or wide composition label depending the different number of compositions to show.

For example, from a query I should get that there are 2 diferent types of percent-materials I have.

fabric.id material.percent material.material.description
36033622 80.0 COTTON
36033622 20.0 POLYESTER
36033621 20.0 POLYESTER
36033621 80.0 COTTON

So I would like to concat material.percent and material.material.description and count how many diferent values does it have.

Kind regards:
bianca
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bianca Bromonti wrote:So I would like to concat material.percent and material.material.description and count how many diferent values does it have.

Have a look at the CONCAT function in the JPQL supported functions section,
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bianca Bromonti wrote:So I would like to concat material.percent and material.material.description and count how many diferent values does it have.

Or you could have a query using the GROUP BY clause with COUNT(*). Something like this query will do the job too and might be even more performant as concatenating 2 or more columnsYou could of course use a native query as well.
 
Bianca Bromonti
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select concat(material.percent, material.material.description).... gives a the following exception

java.lang.ClassCastException: java.lang.String cannot be cast to [Ljava.lang.Object;


Anyway, I would need count(distinct(calculated value)) , and i can't find the way.
It is not necessary to concat 2 fields, it would be enough to add the percent and the material.id in order to be able to count the different values.
For example: count(distinct (material.percent+material.material.id)), but it doesn't function either.


count(distinct(material.percent+material.material.id)) --> org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 2, column 15
count(distinct material.percent+material.material.id) --> org.hibernate.hql.ast.QuerySyntaxException: expecting CLOSE, found '+' near line 2, column 32
 
Dave Tolls
Ranch Hand
Posts: 2099
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try Roel's other suggestion.
That's how I've counted value combinations straddling columns before.
 
Bianca Bromonti
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, not exactly, perhaps i didn't explain well: I need to get how many different percentage-materials are:
20 POLYESTER, 80 COTTON --> 2
3 ELASTHANNE, 5 ELASTHANNE, 95 COTTON, 97 COTTON --> 4


For example the following query

select
count(*),
material.percent,
material.material.description
.....
group by material.percent, material.material.description

gives the following results:
CASE1.
2 20.0 POLYESTER
2 80.0 COTTON


CASE2.
1 3.0 ELASTHANNE
1 5.0 ELASTHANNE
1 95.0 COTTON
1 97.0 COTTON


In CASE1 the max(count(*)) would give 2, but in CASE2 it would give 1.

I need a single number as a return of the query, since it is a part of a bigger query.
 
Dave Tolls
Ranch Hand
Posts: 2099
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your other attempts won't achieve that either, using distinct and concatenation.

What are you going to do with the data?

I'm thinking in terms of GROUP BY on id, percentage and material, with the count(*), ORDER BY id.
Then take the resultant list and do whatever with it to produce some suitable model.
 
Dave Tolls
Ranch Hand
Posts: 2099
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually scratch that...you don't actually want the id, and it doesn't help you anyway.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bianca Bromonti wrote:I need a single number as a return of the query, since it is a part of a bigger query.

So if I understand correctly, given the following values the query should return 4. Because there are 4 different combinations of percentage and material.This query with subquery in the FROM clause will return the expected value 4The only problem with this solution: using a subquery in the FROM clause is not supported in JPA (unless you are using EclipseLink/TopLink).

Using DISTINCT instead of GROUP BY works as well, but again with a subquery in the FROM clauseSo maybe you can use a native query instead. But as it seems to be part of a bigger query that might not be the case.

Hope it helps!
Kind regards,
Roel
 
Dave Tolls
Ranch Hand
Posts: 2099
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The key to look at is in the data, which I think the following might be an example (expanding on what was given):

36033622 80.0 COTTON
36033622 20.0 POLYESTER
36033621 20.0 POLYESTER
36033621 80.0 COTTON
12345678 3.0 ELASTHANNE
12345678 97.0 COTTON

Those are three pieces of cloth (or items of clothing, whatever).
Two of them are 80/20 cotton/polyester, and one is 97/3 cotton/elastane.

So what is required as an output is something like:
80 cotton, 20 polyester, 2
97 cotton, 3 elastane, 1

So a list of materials and percentages that make up the thing, and how many things have that breakdown.

Hope I'm reading this correctly!

(Note, I haven't actually got a solution).
 
Bianca Bromonti
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need number of the different percent-material cases.

for example, the result for this is: 4
36033622 80.0 COTTON
36033622 20.0 POLYESTER
36033621 20.0 POLYESTER
36033621 80.0 COTTON
12345678 3.0 ELASTHANNE
12345678 97.0 COTTON

 
Dave Tolls
Ranch Hand
Posts: 2099
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A count on a SELECT with a GROUP BY those columns?

What have you tried?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bianca Bromonti wrote:for example, the result for this is: 4
36033622 80.0 COTTON
36033622 20.0 POLYESTER
36033621 20.0 POLYESTER
36033621 80.0 COTTON
12345678 3.0 ELASTHANNE
12345678 97.0 COTTON

Did you try one of the queries in my previous post? Both will return 4 when executed on this dataset.
 
Bianca Bromonti
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you, but in HQL I cannot use these queries.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bianca Bromonti wrote:but in HQL I cannot use these queries.

That's true! You can't use subqueries in FROM clauses, although it could be supported by some JPA providers. Unfortunately I can't think of any other query to obtain the same result. Is it not an option to use a native query for this? Or do some (limited) processing in Java after executing a JPQL query to obtain the desired result?
 
Dave Tolls
Ranch Hand
Posts: 2099
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SELECT material.percent, material.description, count(material) FROM material GROUP BY material.percent, material.description

Wouldn't that do it?
 
Bianca Bromonti
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need one retuning number. And this query does different thing what i need. I need the number of different percentage-material combination.
 
Dave Tolls
Ranch Hand
Posts: 2099
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh yes.
Well, take that query and then manually count in Java.

As Roel says, that's probably what you're looking at if you can't use a native query.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic