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

why do you feel you need to use GROUP BY?

also, is this mysql?

Yes, I need a group by to use as a trigger for iReport, so a page break is send.

The database used is: Derby

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"

so why did you re-post this question in the mysql forum?

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.

are you serious?

we are not mind readers

good luck with your problem, and please, try not to be so hostile

[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.

i could not agree more

What is this suppose to mean?

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.