?
Solved

SQL max aggregate function

Posted on 2011-04-28
3
Medium Priority
?
279 Views
Last Modified: 2012-05-11
I am trying to get the maximum from a result set so I can avoid receiving duplicates rows.  The field in question is totalgifts from the gifts_VIEW_donor_relations_simple table.  The field already contains the total for all gifts given, but it's duplicated for each fund (ie. if someone has given a total of $1000 but split it evenly between the building fund and the athletic fund, there would be two lines in the table with $1000 in each line in the totalgifts column).  The query I have setup is below:

use vcu

select c.coreid as ConstituentCoreID
      ,c.millid_married as MarriedID
      ,n1.namesmashd as BoardNameSort
      ,n1.nameformn as BoardName
      ,n.namesmashd as ConstituentSort
      ,n.nameformn as Constituent
      ,r.relstrtdat as StartDate
      ,r.relstopdat as StopDate
      ,r.rellook1 as Position
      ,r.relisa as Relationship
      ,cn.table_val ConstituentCode
      ,f.table_val as FoundationType
      ,d.deathdate as ConstituentDeathDate
      ,d1.deathdate as DisbandedDate
      ,g.totalgifts as TotalGifts
      ,i.intaddress as EMail
from corebio_VIEW_married_surviving c
      left outer join relation_full r on c.coreid=r.relid
      left outer join corebio_full c1 on r.relrelatid=c1.coreid
      join foundation_types f on c1.corefntype=f.table_code
      left outer join names_VIEW_preferred n on c.coreid=n.nameid
      left outer join names_VIEW_preferred n1 on c1.coreid=n1.nameid
      left outer join death_full d on c.coreid=d.deathid
      left outer join death_full d1 on c1.coreid=d1.deathid
      join constituent_types cn on c.coreconst=cn.table_code
      join gifts_VIEW_donor_relations_simple g on c.coreid=g.coreid      left outer join internetaddress_VIEW_preferred_email_NOT_INVALID i on c.coreid=i.intid
where f.table_type='board'
order by ConstituentSort
0
Comment
Question by:lcallah93
  • 2
3 Comments
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 2000 total points
ID: 35486224
Use MAX(g.totalgifts) and GROUP BY the rest of the fields.
0
 

Author Closing Comment

by:lcallah93
ID: 35486472
OMG - that was so EASY!!  Thank you, thank you, thank you!!!
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35486559
You are welcome. Glad to help. :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question