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
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.
posted 6 years ago
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
posted 6 years ago
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
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