SQL Query in Crystal Report Developer 9.0: Group by feature

Hi to all,

How to add in group by feature in SQL query of Crystal Developer 9.0.

I using a Oracle 9.0 database accessing two inner joined tables.
I tried the sql command in Database Expert feature, but it's not what I required.
I need to see the group by in SQL statement in SQL Query feature.
Could anyone will kindly help me please?
Thank anyone in advance.

I also tried to use it in a SQL Command in Crystal reports which results in the following error message.
Query Engine Error: ORA-00979: not a GROUP BY expression

If the query contains a GROUP BY clause, then every item in the select list MUST be an aggregate such as SUM or COUNT which is what the message means by "a GROUP BY expression". This is standard ANSI SQL and will be true in any SQL database, not just Oracle.

However, that is not to say that you can't create a non-GROUP BY query and then have Crystal create any groups you want. In the situation that you mention, the underlying query will simply not have the GROUP BY clause added to it and will then return all detail level rows. The summarization will be done on the front-end by Crystal reports. Just look at the Show SQL in Crystal and I'm sure that you will see that there is no GROUP BY added to the query.

A GROUP BY query is only useful if you have no desire to see the employee level detail, but only summaries. In this case, getting Crystal to generate a GROUP BY is an optimization tactic since much less data needs to be returned, the summarization is done on the server, and therefore the report will run faster. It is even good if you initially hide the detail but expect your users to drill down on a limited number of the groups. In this case, Crystal will generate a second query to get the detail for only the group that has been drilled into (Again check the Show SQL). If you expect your users to drill into a large number of the groups, then just fetching all the detail initially is better.

In summary, adding a group to a Crystal report DOES NOT add a GROUP BY clause to the SELECT query that Crystal generates unless grouping on the server is on and the detail is hidden or suppressed. This is easy to verify by looking at the Show SQL. Crystal computes the aggregates in any report groups and does not rely on getting them from the server. There is no correspondence between groups in the report and GROUP BY clauses in the database query if the detail sections are displayed.

Even in the situation where the generated query does have a GROUP BY (detail is hidden), the database query can only return information at the lowest level of non-hidden report group. So if there was a grouping above manager for instance, Crystal would have to compute the group fields for the higher level groups.

I'm sorry if I've been overly ademate about this, but I think it is important to understand. It could be that you are seeing an ORDER BY being added to your queries, but that is a completely different thing than a GROUP BY. Crystal will always add an ORDER BY for any groups that are created in the report, but that only sorts the detail rows it does not aggregate them.

In order for the Crystal generated query to contain a GROUP BY statement, there must be at least one group section in the report, the database or report option for grouping on the server must be checked, and the detail sections of the report must be suppressed. There may be other requirements as well, but these are the basics. In short, you must ensure that no detail level data is required or displayed and then Crystal will generate a GROUP BY clause for the lowest level of grouping in the report.

You could, of course, write the query however you want it in a SQL Command.

I am suprised that you state that the detail sections don't have to be suppressed. When a SQL query has a GROUP BY clause, no detail data will be returned - no row level data - only summaries at the group level. Hence, if the detail sections are NOT suppressed, Crystal MUST return the detail level rows in order to display them, and cannot use a GROUP BY clause. I believe the detail sections must be suppressed, and this is my experience as well as the logical conclusion.

If you drill down in a report that has detail suppressed and which is backed by a query with a GROUP BY clause, Crystal will generate a second query to return the detail level data for only that group. I have verified this by checking the queries from the Oracle side.

I just built one in CR7 that didn't need the detail section suppressed. I also have many reports at work with group by clauses that I use and display details from each record. I am using CR7 and CR8, is it possibly a new "feature" of CR9?

This is an excerpt from the Crystal Reports Help file.
"GROUP BY
The GROUP BY clause retrieves a set of summary data. Instead of retrieving the data itself, GROUP BY groups the data and summarizes each group with an SQL aggregate function. The server returns only the summarization information for each group to Crystal Reports."

A SQL SELECT statement that contains a GROUP BY clause does not return 'the data itself' meaning the detail level data, so a query containing a GROUP BY cannot be displaying any detail data. It's not a new feature of CR9. It's a property of a GROUP BY query.

I am very interested in how this could be happening for you. I don't mean to belabor a point, but I'd really like to get this figured out.

Let's take an example. If I create a report based on the Scott.EMP table from the Oracle sample database that displays empno, ename, job, and mgr, and group on mgr with a count of empno for the group, and DO NOT suppress the detail, the following query (with no GROUP BY clause) results and Crystal itself is computing the count by manager.
SELECT "EMP"."EMPNO", "EMP"."ENAME", "EMP"."JOB", "EMP"."MGR"
FROM "SCOTT"."EMP" "EMP"
ORDER BY "EMP"."MGR"

If I then suppress or hide the detail section and grouping on the server is checked, the following query results with a GROUP BY clause.
SELECT "EMP"."MGR", COUNT("EMP"."EMPNO")
FROM "SCOTT"."EMP" "EMP"
GROUP BY "EMP"."MGR"
ORDER BY "EMP"."MGR"

Now with this query, no employee level data has been returned, only the manager and the count for that manager. How could Crystal possibly display say empno when it was not even returned by this query?

Perhaps there is some kind of symantic misunderstanding here or something, because I am mystified as to how any GROUP BY query could be used to display row level data.

This would then allow me to have a group header and footer for the manager's information and the detail section for each employee. I need no summary data but I do need the details. I do this in numerous reports.

Is it possible we are talking about 2 different types of GROUP BY clauses since you refer to a GROUP BY query?

Again, I apologize, but your pronouncements are (rightfully) well respected here and I don't want to even inadvertently be the cause of misinformation.

It does work exactly the same way with Access and SQL Server. It is a standard SQL language sort of thing.

To demonstrate, I created a report against the sample Northwind database installed with SQL Server, using the Employees database, displaying EmployeeID, LastName, FirstName, and ReportsTo, and grouping on Reports to with a summary field containing the count of EmployeeID. The first listing below is the Show SQL that results when the detail section is not hidden or suppressed. The second listing is the Show SQL that results when the detail section is hidden.

And results in this error message when used in a SQL Command from Crystal Reports.

"Failed to open a rowset.
Details 42000:[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'Employee ID' as part of an aggregate function."

"Failed to open rowset."
"Query Engine Error:42000[Microsoft][ODBC SQL Server Driver][SQL Server] Column 'Employees.EmployeeID is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

When a GROUP BY is used in a SELECT statement, all the expressions in the select list must be either the group by fields themselves or aggregates for any other fields.