title: How to use Derby database with Using Join And aggregate functions together?
body: <p>I have  two table in my data base As below:</p>
  
  <pre><code>CUSTOMER (
    ID integer Generated Always As Identity,
    FIRSTNAME varchar(32) not null,
    LASTNAME varchar(32) not null,
    PHONENUMBER varchar(11) not null,
    ADDRESS varchar(225),
  primary key (id)
  );
  </code></pre>
  
  <p>and</p>
  
  <pre><code>APP.ORDERS (
    ID integer not null default AUTOINCREMENT: start 1 increment 1,
    CUSTOMER_ID integer References Customer(ID) ,
    EMPLOYEEID integer References Employee(ID),
    DATEOFDELIVERY date,
    primary key (id),
  ); 
  </code></pre>
  
  <p>When I try To join This two table as below every thing is fine:</p>
  
  <pre><code> SELECT CUSTOMER_ID,firstname,LASTNAME,Address,phoneNumber From APP.Customer 
   INNER JOIN APP.Orders ON customer.ID = customer_ID 
   WHERE ORDERS.dateOfDelivery Between '2012-09-01' AND '2012-11-11'
  </code></pre>
  
  <p>but When I use Aggregate Function as below it give me Error:</p>
  
  <pre><code>SELECT Count(CUSTOMER_ID),firstname,LASTNAME,Address,phoneNumber 
  From APP.Customer                         
  INNER JOIN APP.Orders ON customer.ID = customer_ID 
  WHERE ORDERS.dateOfDelivery Between '2012-09-01' AND '2012-11-11' ;
  </code></pre>
  
  <p>Error:</p>
  
  <pre><code>[2012-07-20 08:07:04] [42Y35][30000] Column reference 'FIRSTNAME' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.  
  </code></pre>
  
  <p>I try to change the last code above to Orders.Count(CUSTOMR_ID) to tell the database the correct table but it give me error that there is no schema with name Orders so I change it to APP.Orders.Count(CUSTOMER_ID) but it never solve the problem.
   Error :<code>Schema 'ORDERS' does not exist</code></p>
  
  <p>UPDATING QUESTION:</p>
  
  <p>I Figur out that very simple Query Like :</p>
  
  <pre><code>SELECT COUNT(*),ID FROM Orders
  </code></pre>
  
  <p>will caused to This Error :</p>
  
  <pre><code>[2012-07-20 09:01:50] [42Y35][30000] Column reference 'ID' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions. 
  </code></pre>
  
  <p>So It means that it is not possible to have both aggregate function and simple column names together but I am wonder how to supply the expected out put with any equivalent Query?<strong>Any Idea?</strong> </p>