Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Question on "Group by" for Derby

 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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


 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Martin,
I understand that SQLite is quite different from JavaDB.
And I am making progress.
Now I am on this query


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
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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

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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic