[SOLVED] how to use groupby with 2 tables

[suhaib1thariq]

i have sql query..it is havin gtwo tables with a groupby ..i seems it is not working in derby


the query is

 SELECT MarkInfo.RegNumber as REGISTER_NUMBER, Name as NAME ,sum(Internal +Extern) AS TOTAL,avg(Internal + Extern)AS AVERAGE 
from MarkInfo,StudentInfo 
where StudentInfo.RegNumber = MarkInfo.RegNumber 
group by MarkInfo.RegNumber

when i executed this i got error like

Error code -1, SQL state 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.


how can i execute that in derby
any alternative ..

[DarrylBurke]

edit Wait let me think.

[masijade]

Um, I believe you would also have to group by name (as well as register number). That field is neither an aggregate (i.e. sum, max, min, somethingLikeThat), nor are you grouping over it, which would cause this sort of error. I know, that you probably only have one name per reister number, but you still need to do something with that field, and if you do have just one name per register number, then there is no problem grouping over both fields, anyway.

[DarrylBurke]

@masijade: is this special to Derby? The query posted works fine in VFP. In case of multiple records with the same RegNumber, the Name field would be filled in from the first one encountered.

Thanks, db

[Steve11235]

Masijade is correct, and that is standard SQL. Visual FoxPro doesn't necessarily adhere to the standards.

[suhaib1thariq]

Thanks it works in DB now.The syntax i previously posted worked in mysql, is the SQL syntax very different for DB?.

[masijade]

Not all DBs adhere to the SQL 92/98 standards "religiously", but the differences are normally not all that great.

For the most part, as long as you stick to the standards, you're ok.

[DarrylBurke]

Thanks, masijade and Steve.

db

[Steve11235]

My impression is that MySQL and Derbe both follow the standards closely, especially on basic syntax like group by.

One thing that has helped me immensely with SQL is using interactive query applications. I don't know if Derby has one, but I'm sure MySQL does, and many IDE's come with them built-in. The advantage is that you type in the statement and run it, and you get results and messages back immediately. You can then modify the statement and run it again. I build more complex statements by starting with a core statement and then building on to it.

[masijade]

My impression is that MySQL and Derbe both follow the standards closely, especially on basic syntax like group by.

One thing that has helped me immensely with SQL is using interactive query applications. I don't know if Derby has one, but I'm sure MySQL does, and many IDE's come with them built-in. The advantage is that you type in the statement and run it, and you get results and messages back immediately. You can then modify the statement and run it again. I build more complex statements by starting with a core statement and then building on to it.

Actually, MySQL is one of the DB's that deviate the most from the standards (at least in the form of extensions). (At least among the truely mainstream DBs). They have a few data types that are not standard, and, per default, unless explicitly configured away, fill in "null" dates with "0000-00-00", and the like, and other things of that sort. Only one of the possible engines supports foreign keys and transactions.

For some more info see MySQL :: MySQL 6.0 Reference Manual :: 1.7 MySQL Standards Compliance