Java Hibernate JPQL Query (Aggregate Function: count)

Question

During a join I get the following Table (example):

+----------+----------+
| Hostname | Severity |
+----------+----------+
| host1    |   high   |
| host2    |  medium  |
| host1    |   high   |
| host2    |   low    |
| host1    |   low    |
| host2    |   low    |
| host1    |   low    |
| host2    |   high   |
| host1    |   high   |
| host2    |   high   |
+----------+----------+

Is it possible to create I JPQL query where I get the following result:

+----------+------+--------+-----+
| Hostname | high | medium | low |
+----------+------+--------+-----+
| host1    |  3   |   0    |  2  |
| host2    |  2   |   1    |  2  |
+----------+------+--------+-----+

I tried with COUNT and GROUP BY but I got something like that:

host1,high,3
host1,medium,0
host1,low,2

etc...

BR, Rene

Derek · Accepted Answer · 2011-08-15 12:05:25Z

1

A standard sql query would look like this - I'm not sure what effort is required to the API you are using, but its a pretty straight-forward group by with case logic.

select hostname, 
  sum(case when severity = 'high' then 1 else 0 end) as high,
  sum(case when severity = 'medium' then 1 else 0 end) as medium,
  sum(case when severity = 'low' then 1 else 0 end) as low
from
  Table
group by
  hostname
order by
  hostname

answered Aug 15, 2011 at 12:05

Derek

23.5k8 gold badges55 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thomas · Accepted Answer · 2011-08-15 12:05:16Z

0

Actually, your result looks fine, it's just one row per "cell".

If you want to have a column per Severity entry, you'd have to use subselects, which is not worth the hassle, IMO. Just read the rows you get and convert them into the matrix format manually.

answered Aug 15, 2011 at 12:05

Thomas

88.9k13 gold badges126 silver badges162 bronze badges

1 Comment

reen Over a year ago

Thanks for the answer, but I didn't want to do that additionally. The above works and I can get the wantet result in a single query.

Collectives™ on Stack Overflow

Java Hibernate JPQL Query (Aggregate Function: count)

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related