Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: group by and max Post Reply Post New Topic
Author Message
luarit
Newbie
Newbie


Joined: 22 Aug 2011
Location: Spain
Online Status: Offline
Posts: 6
Quote luarit Replybullet Topic: group by and max
     Posted: 22 Aug 2011 at 10:48pm
Hello all,
 
I have a issue with crystal report. I explain you, if I have 2 tables: vendors and card:
 
Vendors:
 
Name       Surname           Age    Address     Birthday
 
Paul           Gutierrew        34        A                w
Jacques     Gomez             54        B                x
Mary          Smith               67        C                y
David         MA                   23        D                z
Dani           Torre               27       E                 m
 
Card
 
Name          Color        Company              Last Modification
 
Paul              Red          Seat                         20/01/1999
Jacques        Red           Renault                   15/09/2008
Mary             Green       Porche                     13/06/2008
David           Green        Renault                   15/09/2007
Dani             Red           Citroen                    15/09/2004
David           Green        Renault                   11/05/2011
Dani             Red           Citroen                    23/08/2010
 
In sql I will have:
 
SELECT Vendors.Name, Vendors.sURNAME, Vendors.Age, max(Card."Last Modification")
FROM vendors , Card
WHERE VENDORS.Name=Card.Name
group by  Vendors.Name, Vendors.sURNAME, Vendors.Age
 
But I dont know how i can do group by (several fields) in Crystal reports. If I do the max as a formula I will have a duplicated data, I need to do a group by from several fields.
 
Any Idea?
 
Thanks
IP IP Logged
sharona
Senior Member
Senior Member
Avatar

Joined: 16 Oct 2008
Location: United States
Online Status: Offline
Posts: 255
Quote sharona Replybullet Posted: 23 Aug 2011 at 3:12am
in crystal if you create a group and place the fields in the group it will only return the 1st unique record it hits.
what happens if you add the as LAST_MOD
and create a group by that?
SELECT
Vendors.Name,
Vendors.sURNAME,
Vendors.Age,
max(Card."Last Modification") as LAST_MOD
FROM
vendors , Card
WHERE VENDORS.Name=Card.Name
group by  Vendors.Name, Vendors.sURNAME, Vendors.Age
sharona
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 23 Aug 2011 at 3:13am
SELECT Vendors.Name, Vendors.sURNAME, Vendors.Age, ss.LastMod
FROM vendors 
join Card on VENDORS.Name=Card.Name 
join (select max("Last Modification") as LastMod, Name from Card group by Name) as ss
  on ss.Name = card.name = ss.Name and card."last modification" = ss.LastMod
group by Vendors.Name, Vendors.sURNAME, Vendors.Age
 
you could have done the subselect in the where clause, but I try and keep function out of the where clause as I have heard that they slow down the select...besides you are really looking to filter out from the results the duplicates.
 
HTH
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 23 Aug 2011 at 3:18am
Sharona's method would return a group, but I would think that there still be 2 records in the group, as both would satisfy the condition and be grouped together...I could be wrong, but that is what I would expect.
 
If you want to filter out the duplicates to begin with...
but if there is other information in the duplicates that you want/need then the join to the subquery would filter that extra data out as well.
IP IP Logged
luarit
Newbie
Newbie


Joined: 22 Aug 2011
Location: Spain
Online Status: Offline
Posts: 6
Quote luarit Replybullet Posted: 23 Aug 2011 at 3:39am
Thanks guys, I found the way to do a max, but now..the problem is that I create a group with expert group and I concatenate  Vendors.Name, Vendors.sURNAME, Vendors.Age (Vendors.Name& Vendors.sURNAME& Vendors.Age). I made the max of data: Maximum ({LAST_MOD},{@Concatenate}) and I can obtain the good last modification.
 
The problem is that I have duplicated records, I have something like:
 
Paul           Gutierrew        34      20/01/1984
Jacques     Gomez             54      15/09/2008
Mary          Smith               67      13/06/2008
David         MA                   23      11/05/2011
David         MA                   23      11/05/2011
Dani           Torre               27      23/08/2010
Dani           Torre               27      23/08/2010
 
I want only one row, any idea?
 
with SELECT DISTINC RECORDS didnt work
 
Thx
IP IP Logged
JFinzel
Groupie
Groupie
Avatar

Joined: 20 Jul 2011
Location: United States
Online Status: Offline
Posts: 49
Quote JFinzel Replybullet Posted: 23 Aug 2011 at 7:10am
This may not be the best solution, but if there is an exact value that certainly differs between each record, like a Vendor ID, then you can suppress duplicates.  You need that to be distinct because of course if two people have the same name or date, you are going to get in trouble suppressing on that condition.  Go to Select Expert for the Details section, and click on the formula bar by "suppress", you can do a formula to suppress duplicates:

Next ({table.vendor ID})={table.vendor ID}

Thus if the next record as identical with the first, this formula will suppress the first record.  Or:

Previous ({table.vendor ID})={table.vendor ID}

This will suppress the second duplicate row.

Again, not sure if this is the best solution but maybe give it a try.


Edited by JFinzel - 23 Aug 2011 at 7:13am
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.