0 Replies Latest reply on Aug 3, 2005 3:35 AM by catherine lo

    SELECT COUNT...GROUP BY declared-sql

    catherine lo Newbie

      I tried to execute the following query using JBoss declared-sql:
      SELECT c.Status, Month(c.firstUsed), COUNT(c.serialNumber) FROM Card c WHERE c.status = 4 GROUP BY Month(c.firstUsed)

      I have the following in the jbosscmp-jdbc.xml:


      < select >
      < alias ><![CDATA[c]]>< /alias >
      <additional-columns><![CDATA[, Month(c.firstUsed), COUNT(c.serialNumber)]]></additional-columns>
      < /select >
      < where ><![CDATA[ c.status = 4 GROUP BY Month(c.firstUsed)]]>< /where >

      And I have the following method in the Card Entity Bean:

      * @ejb.home-method view-type="local"
      public Collection ejbHomeCountCardsUsed() throws FinderException {
      return ejbSelectCountCardsUsed();

      * @ejb.select query=""
      public abstract Collection ejbSelectCountCardsUsed() throws FinderException;

      When I read from the Collection returned by ejbHomeCountCardsUsed(), I was only be able to get the c.Status value. How can I get the Month(c.firstUsed), COUNT(c.serialNumber)?

      Or is there any better way to implement this SELECT COUNT...GROUP BY query?