Question on "Group by" for Derby

[Jacky Luk]

I have an interesting question on Derby
While Derby doesn't allow me to run such a query below
without grouping every single column, but if I do so,
actually the resulting resultset would be wrong,
The sum is not grouped, and
the ResultSet is iterated twice
one for each order code
So what's is the correct way of grouping things
in Derby, I have it working in SQLite
Now I switched vendor.
Thanks a lot
Jack

 String str = "SELECT "
               
                 + " o.OrderCode AS 订单ID,"
                + " o.PurchaseDateTime AS 订单时间,"
              
                + " od.ProductCode AS 产品ID,"
                + " p.ProductName AS 产品名称,"
                + " t.TextureName AS TextureName, "
                + " s.TextureName AS 产品分类名称,"
                + " p.UnitPrice AS 单位价,"
                
                + " od.Quantity AS 数量, "
                //+ " od.GrossSales  AS 销售总额, "
                //+ " od.Discount AS 折扣," 
  
                +" NULLIF(sum(od.NetSales),0) AS 总销售额"
                 
                
   
                              
                + " FROM ((((((customer  c "
                
                + " INNER JOIN orders  o ON (o.CustomerCode = c.CustomerCode ))"
                + " INNER JOIN order_details od ON (od.OrderCode = o.OrderCode ))"
                + " INNER JOIN products  p ON (od.ProductCode = p.ProductCode ))"
                + " INNER JOIN subtexture s ON (s.SubTextureID = p.SubTextureID ))"
                + " INNER JOIN texture t ON (s.TextureID = t.TextureID))"
                + " INNER JOIN employee  e ON (o.EmployeeCode = e.EmployeeCode ))"
                + " where c.CustomerCode = '" + custID + "'"
                + " group by o.OrderCode"; /*, o.PurchaseDateTime, od.ProductCode, p.ProductName, t.TextureName,"
 
                    + "  s.TextureName, p.UnitPrice, od.Quantity,  od.NetSales  ";*/

[Martin Vashko]

The point of GROUP BY is to obtain aggregate values of columns you don't group by (if you don't want any aggregate value at all, you can as well use the DISTINCT keyword). So for each column, you need to either group by it, or use some aggregate function. Some databases won't complain if you select a non-grouped, unaggregated column, and will handle it somehow (you'd have to consult the documentation to find out what the DB does with such a column), but this relaxation goes over what the SQL standard defines (or so I believe). So, Derby is actually right when it refuses to process your statement.

If you got it working in SQLite, and it provided something different from what Derby provides when you specify the group by for all the columns, then you need to go back to the SQLite documentation and find out what that command actually did there, and find a way to do this in Derby. I believe that some databases use the FIRST aggregate function when none is specified for an ungrouped column, but I may be wrong. I do suspect, though, that the statement you deemed working in SQLite might have actually done something different from what you thought it does, as not specifying an aggregate function for an in grouped column is typically an oversight.

[Jacky Luk]

Hello Martin,
I understand that SQLite is quite different from JavaDB.
And I am making progress.
Now I am on this query

select e.LogCode AS 交易编号, e.OrderCode AS 订单ID,  e.When0 AS 日期时间,
ac.ActivityName ||  ' ' || e.Details AS 交易或服务项目, 
s.NetSales AS 金额, e1.LastName || ' ' ||  e1.FirstName  AS 负责人, 
e.NeedFollowUp AS 须要跟进,  e.FollowUpExpiration AS 跟进日期,   
e.When0 AS dt 
from elog  e 
, 

                   
(select   g.CUSTOMERCODE, g.LogCode, g.OrderCode,
 e.LastName AS LastName, e.FirstName AS FirstName
from employee e LEFT JOIN 
elog g ON
(g.HandledBy = e.EmployeeCode)
group by g.LogCode,  g.OrderCode, e.LastName, e.FirstName, g.CustomerCode ) e1
 
 
, activity ac , 
  (select e2.LogCode, e2.CUSTOMERCODE, o.ORDERCODE,
   od.NetSales AS NetSales  from elog e2 INNER JOIN
   orders o ON (e2.OrderCode = o.OrderCode) INNER JOIN
   order_details od ON (o.ORDERCODE = od.ORDERCODE)
   group by e2.LOGCODE, e2.CustomerCode, od.NetSales,
   o.OrderCode ) s
 
where  e.LogCode = e1.LogCode 
AND ac.ActivityID = e.Activity 
AND s.LogCode = e.LogCode 
AND e.CUSTOMERCODE = s.CustomerCode
AND e.CUSTOMERCODE = e1.CustomerCode
 
 AND e.CUSTOMERCODE = 'CUS000001-2013-11-06'
group by  e.LogCode, e.OrderCode,
e.When0, ac.ActivityName, e.Details, s.NetSales,
e1.LastName, e1.FirstName, e.NeedFollowup,
 e.FollowUpExpiration, e.When0, e.CUSTOMERCODE 
order by dt DESC

However, with the same customer, the same order is returned twice without grouping,
Now notice I don't add in comparison for order code because some logs
do not contain a order code, so the only problem is repeating rows.
Do I have to aggregate something? I think not, because I want each
LogCode associated with the order detail code to be unique
Say Log1 for customer 1 making $100 sales
then Log2 for customer 1 making $200 sales etc
Any ideas?
Thanks
Jack

[Jacky Luk]

select e.LogCode, e.ORDERCODE, od.NetSales, e.CUSTOMERCODE  from elog e
        INNER JOIN orders o ON (e.ORDERCODE = o.ORDERCODE)
        INNER JOIN order_details od ON (o.ORDERCODE = od.OrderCode)
      group by e.LogCode, e.OrderCode, e.CustomerCode, od.NetSales

Back to the basics, This returns every order item sold
for each customer for each order,
I don't want that, I'd like to have only group by
e.LogCode, I understand the repeating groups
are caused by the multiple group columns

Update
I use select distinct this time, However, it still has repeating rows

select distinct e.LogCode AS 交易编号,  e.ORDERDETAILID, e.OrderCode AS 订单ID,
e.When0 AS 日期时间, ac.ActivityName ||  ' ' || e.Details AS 交易或服务项目, 
s.NetSales AS 金额, e1.LastName || ' ' ||  e1.FirstName  AS 负责人, 
e.NeedFollowUp AS 须要跟进,  e.FollowUpExpiration AS 跟进日期,    
e.When0 AS dt  
from elog  e  
INNER JOIN   
 
(select distinct g.LogID,  g.CUSTOMERCODE, g.LogCode, 
g.ORDERDETAILID, g.OrderCode, e.LastName AS LastName, 
e.FirstName AS FirstName 
from employee e LEFT JOIN  
elog g ON 
(g.HandledBy = e.EmployeeCode) 
group by g.LogID, g.LogCode,  g.OrderDetailID, 
g.OrderCode, e.LastName, e.FirstName, g.CustomerCode ) e1
ON e.LogCode = e1.LogCode 

INNER JOIN  
(select distinct e2.ORDERDETAILID, e2.LogCode, e2.CUSTOMERCODE, 
od.NetSales AS NetSales  from elog e2 INNER JOIN 
order_details od ON (e2.ORDERDETAILID = od.ORDERDETAILID) 
group by e2.OrderDetailID,  e2.LOGCODE, e2.CustomerCode, 
od.NetSales) s
ON e1.OrderDetailID = s.OrderDetailID
 
 , activity ac  
where    
ac.ActivityID = e.Activity  
AND  e.CUSTOMERCODE = 'CUS2-2013-11-21'
group by e.LogID, e.LogCode, e.ORDERDETAILID, 
e.OrderCode,  e.When0, ac.ActivityName, e.Details, s.NetSales, 
e1.LastName, e1.FirstName, e.NeedFollowup, e.FollowUpExpiration,
e.When0, e.CUSTOMERCODE  
order by dt DESC
 

Do I have to use group by with select distinct, I tried to cancel the group by
in select distinct, but it results in the same resultset