title: How to call SQL query with aggregate function in JPA/Hibernate
body: <p>Is it possible to run such a a query like this below in JPA/Hibernate?</p>
  
  <pre><code>select v.country,
  v.site,
      SUM(case when s.id = 1 then 1 else 0 end) as Total_SuspectedViolations,
      SUM(case when s.id = 2 then 1 else 0 end) as Total_ConfirmedViolations,
      SUM(case when s.id = 3 then 1 else 0 end) as Total_ConfirmedNoViolations,
      SUM(case when s.id = 4 then 1 else 0 end) as Total_NotDetermined,
      COUNT(*) Total
  from violations v
  inner join status s
      on v.status_id = s.id
  group by v.country, v.site
  </code></pre>
  
  <p>or </p>
  
  <pre><code>select v.country,
  v.site,
      SUM(case when v.status_id = 1 then 1 else 0 end) as Total_SuspectedViolations,
      SUM(case when v.status_id = 2 then 1 else 0 end) as Total_ConfirmedViolations,
      SUM(case when v.status_id = 3 then 1 else 0 end) as Total_ConfirmedNoViolations,
      SUM(case when v.status_id = 4 then 1 else 0 end) as Total_NotDetermined,
      COUNT(*) Total
  from violations v
  group by v.country, v.site
  </code></pre>
  
  <p>This works in Oracle DB but when I am using JPA/Hibernate I am getting:</p>
  
  <pre><code>ERROR [PARSER] line 1:311: unexpected token: total
  ERROR [PARSER] line 1:375: unexpected token: ON
  </code></pre>
  
  <p>I am asking because I need to implement some simple statistics based on data in one table "Violations"</p>
  
  <p>Below is my idea:</p>
  
  <p>I need to join two tables and count occurrences some values from second table to achieve effect like statistics table which will have columns:</p>
  
  <p>Result Table:</p>
  
  <ul>
  <li>country varchar(20),</li>
  <li>site varchar(20),</li>
  <li>suspected_violation long,</li>
  <li>confirmed_violation long,</li>
  <li>confirmed_no_violation long,</li>
  <li>not_determined long,</li>
  <li>total long</li>
  </ul>
  
  <p>My Result Table needs to have first two columns (contry and site) comes from first table "Violations" and next 5 columns which will contain numbers (count) of occurrences status_id in "Violations" in each of possible values of id from Status table.</p>
  
  <p>So, I have existing two tables: Violations and Status.</p>
  
  <p>Violations:</p>
  
  <ul>
  <li>id long,</li>
  <li>country varchar(20),</li>
  <li>site varchar(20),</li>
  <li>status_id long, &lt;-- this is the id of status in Status table.</li>
  <li>... other columns not important in this case</li>
  </ul>
  
  <p>Status:</p>
  
  <ul>
  <li>id long,</li>
  <li>status long 
  Column "status" have values (1-4) which are mapped to string values: Suspected Violation (1), Confirmed Violation (2), Confirmed No Violation (3), Not Determined (4)</li>
  </ul>
  
  <p>In result of my join is to have table which should contain columns:</p>
  
  <ul>
  <li>from Violations table: "Country" and "Site"</li>
  <li>from Status table: "Suspected Violation", "Confirmed Violation", "Confirmed No Violation", "Not Determined", "Total" (where this columns are counters of occurrences in Violation table). </li>
  </ul>
  
  <p>UPDATE: As far as I know now there is no need to use join, query can be run on one table only.</p>
  
  <p>Can you suggest me what is the proper way to do that in JPA/Hibernate? </p>