Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2011
    Posts
    6

    Question Unanswered: Group by with multiple files....

    Hi group!

    We are running a helpdesk system on the intranet. People have the possibility sending in a service request which is routed to the right person.

    I'm having trouble finding a good solution for a group by with two files:
    - service request file (seqnr, username, description, reqdate, solvedate)
    - users file (seqnr, username, full name, dept)

    When I perform a group by JUST on the service request file, there's no problem whatsoever. But......I would like to have the full name, department, telephonenr, etcetera on the report header as well.

    The reportgenerator I'm using is iReport. In this program it is possible to use the group by as a trigger for sending a page break. This way all requests are printed with their "rightfull owner".

    Of course, I want to use the username (which is unique) as a group by trigger, but the sql statement issues an error message when I include fields of the users file.

    Here is the sql-statement I'm using:
    SELECT s.*, u.*
    FROM servicereq s, users u
    WHERE s.username = u.username
    GROUP BY s.username
    ORDER BY s.seqnr

    Thanks in advance for your help.

    Kind regards / Med vennlig hilsen,

    Ton

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why do you feel you need to use GROUP BY?

    also, is this mysql?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2011
    Posts
    6
    Yes, I need a group by to use as a trigger for iReport, so a page break is send.

    The database used is: Derby

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ton.wibier View Post
    Yes, I need a group by to use as a trigger for iReport, so a page break is send.
    this makes no sense to me at all, sorry

    i hope you realize that GROUP BY will collapse multiple rows into one result aggregate row

    and you should never mix GROUP BY with the dreaded, evil "select star"

    Quote Originally Posted by ton.wibier View Post
    The database used is: Derby
    so why did you re-post this question in the mysql forum?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2011
    Posts
    6
    First of all, thanks for your reply. But, to be honest, it didn't help me one single bit.

    Secondly, it wasn't my intention to cross-post. Sorry for that.

    Third, of course I'm using the required fields only instead of the star. I was under the assumption that the reader would understand this. Obviously not.

    To come back to your answer: in my post I clearly state what I want. There you can read I want to have the data of the user in the page header and his/her requests as details.

    On the next page, if applicable, the page header is printed again with the rest of the requests of the user.

    Now, when all requests for this user are printed I want to have a page break, of course, so the page header of the NEXT user is printed and his/her request details.

    In iReport I can use a group variable as a trigger to send a page break. That simple!!

    This is what I wrote in my initial posting. How hard can it be to understand this?

    I was hoping for some help, but these kind of answers .... me off.

    If you don't want to answer the question then don't. If you want to contribute positively to this thread, please do so.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ton.wibier View Post
    Third, of course I'm using the required fields only instead of the star. I was under the assumption that the reader would understand this. Obviously not.
    are you serious?

    we are not mind readers

    good luck with your problem, and please, try not to be so hostile
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2011
    Posts
    6
    [OFF TOPIC]
    There you go again! No answer to the question. Not even making the effort helping a novice. That's why I am reacting to you that way.

    Instead of helping people you just want to act as Mr. Knowitall. Don't forget: we all had to start once! It's a pitty that you forget all about this.

    Anyway, I would like to close this chitchat with you, because that's all you contributed in my case.
    [/OFF TOPIC]

    Hopefully other people are willing to help me with this question. And if I'm using the sql statement in a wrong manner, please guide me into the right direction. I am a quick study.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by some anonymous person
    Wenn einer Quatch ist, bist Du es!

    Mensch, Du bist nicht ganz dicht von oben.
    i could not agree more
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2011
    Posts
    6
    What is this suppose to mean?


  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If the audience does not understand the material presented, it is usually not the fault of the audience.

    Crossposting is generally discouraged because it splits the conversation and requires more effort on the part of those trying to help to keep track of facts and troubleshooting results. Crossposting in product-specific forums that don't really apply to the question can make people cranky.

    Now that we have that out of the way...

    When using a GROUP BY clause, any field in your SELECT statement that is not part of an aggregate function must be explicitly listed as a group field. This is why using "*" raised an eyebrow. Since you are a novice you may not know that using "*" in your examples would be taken literally. It will. People actually do that. Full field enumeration is not implied. Now you know.

    wrong (because the SQL engine doesn't know how you intended to aggregate address and moof):
    SELECT username, address, moof, COUNT(foo)
    FROM w00tz
    GROUP BY username

    right (because the SQL engine knows you didn't intend to aggregate address and moof):
    SELECT username, address, moof, COUNT(foo)
    FROM w00tz
    GROUP BY sername, address, moof

    I doubt this will get you to where you need to go with "iReport". Grouping in SQL is used for dealing with aggregate values. The grouping you're talking about seems to deal with categorizing details. I'd be looking for something in iReport that allows you to define grouping levels there. Most banded-report engines have some way for you to do this.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Tags for this Thread

Posting Permissions

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