Databases
StackOverflow2013
Postsdbo
POJPA/JPQL COUNT question
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJPA/JPQL COUNT question
    primarykey
    data
    text
    <p>I have the following JPQL query -</p> <pre><code>SELECT f.md5 FROM File f, Collection leafCollections, Collection instCollections WHERE (f.status = com.foo.bar.FileStatus.Happy OR f.status = com.foo.bar.FileStatus.Sad) AND f.collectionId = leafCollections.collectionId AND leafCollections.instanceCollectionId = instCollections.collectionId GROUP BY f.md5, instCollections.collectionId </code></pre> <p>It basically returns the md5s for files which are organized in a hierarchy (tree) such that if the same MD5 appears in more then one leaf in a particular branch of the hierarchy it will be only shown once (thanks to the GROUP BY). </p> <p>This works fine. Let's say I get 100 rows back. Each row containing an md5 as a string.</p> <p>Now I want to get the <strong>COUNT</strong> of the rows returned. I thought I could simply do:</p> <pre><code>SELECT COUNT(f.md5) FROM File f, Collection leafCollections, Collection instCollections WHERE (f.status = com.foo.bar.FileStatus.Happy OR f.status = com.foo.bar.FileStatus.Sad) AND f.collectionId = leafCollections.collectionId AND leafCollections.instanceCollectionId = instCollections.collectionId GROUP BY f.md5, instCollections.collectionId </code></pre> <p>However this returns 100 rows, each one containing a long representing the number of times the md5 appeared in a branch. What I wanted was simply to get 1 row back with a long value of 100 being the total count of rows the original query returned. I feel like I am missing something obvious.</p> <p>Suggestions? </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTalha Ahmed Khan
    1. USNBW
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POJPA/JPQL COUNT question
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POJPA/JPQL COUNT question
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    3. VO
      singulars
      1. POJPA/JPQL COUNT question
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHmmm.. Did you do query.getSingleResult instead of getResultList (and cast the result to number).?
      singulars
      1. POJPA/JPQL COUNT question
      1. USuncaught_exceptions
    2. COA suggestion: Please place your code ( or query in this case) inside a code block. (Highlight code and press ctrl+k or ^k for mac users )
      singulars
      1. POJPA/JPQL COUNT question
      1. USuncaught_exceptions
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload