Group By and Sum Functions
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1296
Printed Date: 18 May 2024 at 7:12pm
Topic: Group By and Sum Functions
Posted By: srklg1
Subject: Group By and Sum Functions
Date Posted: 10 Sep 2007 at 2:51pm
|
Hey, all! I'm having a little difficulty with the Group By and Sum Functions. The following is SQL generated in Crystal:
SELECT "tblLoads"."BillToName", "tblLoads"."LoadID", "tblLoads"."PickupDate", "tblLoads"."ConsCity", "tblLoads"."ConsSt", "tblLoadDispatch"."DriverPay"
FROM "EXP2000"."dbo"."tblLoadDispatch" "tblLoadDispatch" INNER JOIN "EXP2000"."dbo"."tblLoads" "tblLoads" ON "tblLoadDispatch"."LoadID"="tblLoads"."LoadID" WHERE ("tblLoads"."PickupDate">={ts '2007-09-07 00:00:00'} AND "tblLoads"."PickupDate"<{ts '2007-09-08 00:00:00'}) ORDER BY "tblLoads"."BillToName", "tblLoads"."LoadID" I need to be able to have the records grouped by the LoadID field and then the DriverPay field summed. Multiple records exist with a different value for DriverPay.
Can anyone help?
Thanks!!  ------------- srklg1 |
Replies:
Posted By: hilfy
Date Posted: 11 Sep 2007 at 7:43am
|
Add a group on LoadID to your report. Add a summary field to the report on the DriverPay field. Crystal will handle it - possibly internally, possibly by changing the SQL, so you won't necessarily see a change to the SQL when you set this up in Crystal. -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: srklg1
Date Posted: 11 Sep 2007 at 10:37am
|
Hilfy,
Thanks for the help! I added the Group and Summary to the report, moved the fields to the same line, and received the ouput I wanted.
The SQL, however, didn't generate the Group By clause. How can I do that? I was reading about creating an object and dropping the SQL in SQL Expressions field and tried some of that, but it didn't work.
Any ideas?
Thanks again for your direction! It helped me get done what I needed to get done.
Ciao! ------------- srklg1 |
Posted By: jkwrpc
Date Posted: 11 Sep 2007 at 12:19pm
|
You asked about GROUP BY and I was not sure if you had tried something like this. I did not see it in the SQL statement you posted which is the reason I mention it. You would need to edit the query to match your database requirements.
SELECT tblLoads.BillToName, tblLoads.LoadID, tblLoads.PickupDate, tblLoads.ConsCity, tblLoads.ConsSt, tblLoadDispatch.DriverPay FROM EXP2000.dbo.tblLoadDispatch INNER JOIN tblLoads ON tblLoadDispatch.LoadID=tblLoads.LoadID WHERE tblLoads.PickupDate >= '2007-09-07' AND tblLoads.PickupDate < '2007-09-08' GROUP BY tblLoads.LoadID, tblLoadDispatch.DriverPay ORDER BY tblLoads.BillToName, tblLoads.LoadID Regards,
John W.
http://www.CustomReportWriters.net - www.CustomReportWriters.net
|
Posted By: BrianBischof
Date Posted: 11 Sep 2007 at 12:35pm
|
And make sure you have the setting Perform Grouping on Server checked.
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: srklg1
Date Posted: 11 Sep 2007 at 1:26pm
|
John,
How do I edit the query? I see the Show SQL function, but not Edit Query.
And your Group By statement is exactly what I want to do.
Thanks!!! ------------- srklg1 |
Posted By: srklg1
Date Posted: 11 Sep 2007 at 1:47pm
|
JW,
Where is Edit Query Functionality in Crystal? I'm not finding anything in the Help Files.
Thanks,
SRK BTW, that group by statement is what I want to do and then sum the tblLoadDispatch.DriverPay field. ------------- srklg1 |
Posted By: srklg1
Date Posted: 11 Sep 2007 at 1:48pm
|
Brian,
Where is that checkbox located? I have searched the Help files and poked around the Experts, but I haven't found it yet.
Thanks,
SRK ------------- srklg1 |
Posted By: hilfy
Date Posted: 12 Sep 2007 at 6:51am
|
Edit Query is not available in the more recent versions of Crystal. If you really need to edit the query, you need to use Command Text instead of the normal table selection and linking that you do in Crystal. Command Text is available for some databases - when you connect to the database, just below the connection name you'll see "Add Command" which allows you to enter one or more SQL statements for the report. To get to the "Perform Grouping on Server" go to the File menu and select "Report Options". It's a check-box on this screen. You'll also want to turn on "Use Indexes or Server for Speed."
If you want to set this for all new reports that you create, go to the File menu and select "Options", go to the Database tab and turn on the same two options that I mentioned above.
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: jkwrpc
Date Posted: 12 Sep 2007 at 7:25am
|
Sorry I was not able to get back to you until today. If you use the database expert and follow Hilfy's (Dell) instruction you should be able to create the SQL statement in a command object. I also assume people understand this capability is available.
If you want to sum try this
SELECT tblLoads.BillToName,
tblLoads.LoadID, tblLoads.PickupDate, tblLoads.ConsCity, tblLoads.ConsSt, Sum( tblLoadDispatch.DriverPay) as TotalDriverPay,
tblLoadDispatch.DriverPay FROM EXP2000.dbo.tblLoadDispatch INNER JOIN tblLoads ON tblLoadDispatch.LoadID=tblLoads.LoadID WHERE tblLoads.PickupDate >= '2007-09-07' AND tblLoads.PickupDate < '2007-09-08' GROUP BY tblLoads.LoadID, tblLoadDispatch.DriverPay ORDER BY tblLoads.BillToName, tblLoads.LoadID You will need to watch the sum amount to make sure it is giving the right numbers. The query statement can affect the calculation. As alternate method you can use CR to do the sum calculations.
Hope this helps.
Regards,
John W.
http://www.CustomReportWriters.net - www.CustomReportWriters.net
|
Posted By: srklg1
Date Posted: 12 Sep 2007 at 1:18pm
|
Hilfy,
Thanks for the INFO!!! It was great help! ------------- srklg1 |
Posted By: srklg1
Date Posted: 12 Sep 2007 at 1:24pm
|
John,
I ran the SQL below as written from the Add Command and received multiple records per Load ID, the unique value in our system. For example, we will multiple entries for driver pay like $50 for Inside Delivery, $1000 for Linehaul, and $50 for liftgate. the as written returned 3 lines with the different $ values. I would like one line per Load ID with a total value for DriverPay, $1100 from the example above.
I have created this in MS Access returning one line.
Idea's?
Thanks,
Scott K. ------------- srklg1 |
Posted By: jkwrpc
Date Posted: 12 Sep 2007 at 2:05pm
|
Just group by the LoadId by removing the DriverPay from the Group By statement.
So the Group By would read something like this
Group By tablename.LoadId
I am not able to test the SQL query for obvious reasons but you should get the amount you are looking for. This will pull everything into the single LoadId and not the LoadId plus the three separate DriverPay lines.
Also I did not see any SQL in your post so I cannot comment on it. It sounds like you are getting close to having what you want.
Regards,
John W.
http://www.CustomReportWriters.net - www.CustomReportWriters.net
|
Posted By: srklg1
Date Posted: 12 Sep 2007 at 2:45pm
|
John,
The help is so greatly appreciated! Your suggestion worked and I did get the intended result.
All the best,
Scott K. ------------- srklg1 |