Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Group By and Sum Functions Post Reply Post New Topic
Page  of 2 Next >>
Author Message
srklg1
Newbie
Newbie
Avatar

Joined: 10 Sep 2007
Location: United States
Online Status: Offline
Posts: 22
Quote srklg1 Replybullet Topic: Group By and Sum Functions
    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!!Ouch
srklg1
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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
IP IP Logged
srklg1
Newbie
Newbie
Avatar

Joined: 10 Sep 2007
Location: United States
Online Status: Offline
Posts: 22
Quote srklg1 Replybullet 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
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet 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.
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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>
IP IP Logged
srklg1
Newbie
Newbie
Avatar

Joined: 10 Sep 2007
Location: United States
Online Status: Offline
Posts: 22
Quote srklg1 Replybullet 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
IP IP Logged
srklg1
Newbie
Newbie
Avatar

Joined: 10 Sep 2007
Location: United States
Online Status: Offline
Posts: 22
Quote srklg1 Replybullet 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
IP IP Logged
srklg1
Newbie
Newbie
Avatar

Joined: 10 Sep 2007
Location: United States
Online Status: Offline
Posts: 22
Quote srklg1 Replybullet 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
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet 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.
 
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.032 seconds.