SQL Expression returns multiple rows in Crystal Reports

Question

I'm trying to create a crystal report based of an sql server database. My table has the columns Make. and RentCount, and I'm trying to get the total of the RentCount for the rows that have the same Make.

In Sql Server, I can use the query:

SELECT SUM(RentalCount) AS TotalSales
FROM VehicleInventory
WHERE CarMake='Honda'

and it returns a scalar value with the correct sum. However in crystal reports, when I use the sql expression

(SELECT MAX(SUM(RentalCount))
FROM VehicleInventory
WHERE VehicleInventory.CarMake='Honda')

The sum is displayed in 15 rows having the same value. There are 15 rows affected by my WHERE Clause.

Hello, I don't know about crystal-reports but should you use extra () in the second query? — Ilyes
@Sami - Crystal Syntax for SQL Expression queries requires the entire query to be enclosed within parenthesis. — R. McMillan

MilletSoftware MilletSoftware · Accepted Answer · 2018-09-25T09:46:03

Number of detail rows in a Crystal report is dictated by the result set of the report itself, not by the number of rows of an SQL Expression.

The SQL Expression, like database columns and Crystal formulas, is handled as an additional column in the result set.

SQL Expression returns multiple rows in Crystal Reports

1 Answers