Results 1 to 10 of 10
  1. #1
    suhaib1thariq is offline Member
    Join Date
    Jan 2009
    Posts
    5
    Rep Power
    0

    Angry [SOLVED] how to use groupby with 2 tables

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


    the query is
    Java Code:
     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 ..

  2. #2
    DarrylBurke's Avatar
    DarrylBurke is online now Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,242
    Rep Power
    19

    Default

    edit Wait let me think.
    Last edited by DarrylBurke; 01-25-2009 at 12:02 AM.

  3. #3
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    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.

  4. #4
    DarrylBurke's Avatar
    DarrylBurke is online now Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,242
    Rep Power
    19

    Default

    @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

  5. #5
    Steve11235's Avatar
    Steve11235 is offline Senior Member
    Join Date
    Dec 2008
    Posts
    1,046
    Rep Power
    7

    Default

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

  6. #6
    suhaib1thariq is offline Member
    Join Date
    Jan 2009
    Posts
    5
    Rep Power
    0

    Default

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

  7. #7
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    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.

  8. #8
    DarrylBurke's Avatar
    DarrylBurke is online now Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,242
    Rep Power
    19

    Default

    Thanks, masijade and Steve.

    db

  9. #9
    Steve11235's Avatar
    Steve11235 is offline Senior Member
    Join Date
    Dec 2008
    Posts
    1,046
    Rep Power
    7

    Default

    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.

  10. #10
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Quote Originally Posted by Steve11235 View Post
    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

Similar Threads

  1. Help Printing Tables
    By ogidantunde in forum Advanced Java
    Replies: 0
    Last Post: 06-14-2008, 06:34 PM
  2. caching the tables
    By jayashree in forum JDBC
    Replies: 0
    Last Post: 01-30-2008, 06:43 AM
  3. how to sort 2 tables
    By valery in forum AWT / Swing
    Replies: 1
    Last Post: 08-06-2007, 08:30 PM
  4. Updating into 2 tables in the DB
    By yuchuang in forum New To Java
    Replies: 2
    Last Post: 05-12-2007, 06:54 AM
  5. Tables In Ejb3
    By Alan in forum Enterprise JavaBeans (EJB)
    Replies: 1
    Last Post: 05-10-2007, 04:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •