CODEWITHSUNDEEP
javasqlhibernatejpql
reen
reen

Reputation: 2502

Java Hibernate JPQL Query (Aggregate Function: count)

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

Upvotes: 0

Views: 526

Answers (2)

Derek
Derek

Reputation: 23228

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

Upvotes: 1

Thomas
Thomas

Reputation: 88707

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.

Upvotes: 0

Related Questions