List Family only once

Try playing around with the Distinct keyword

SELECT Distinct [NameCounter]

>In this query the [NameCounter] is a Family ID
>How can I list a family only once.

You'll need to spell out how to display Amount and CNDate if there's only going to be a single row for each NameCounter, for situations where there are multiple rows in your data.

Sum?  Min?  Max?  Don't care?

Sum would be great

Sum would work for Amount assuming it is numeric.
CNDate looks like a date, which aren't sum-friendly, so what to do with CNDate?

So far, if we don't display Date but filter it as it appears in the WHERE clause..

SELECT NameCounter, Sum(Amount) as total_amount
FROM CNHst
WHERE CNDate > '2011-12-31' AND CNDate < '2014-01-01'
GROUP BY NameCounter
ORDER BY NameCounter ASC 

Select allOpen in new window

The last date would be good. This would tell me the most recent contribution date.

Thank you, it works perfectly.

 Can you explain why I receive the following error if I change the Order By to CNDate?

"Invalid in the Order By clause because it is not contained in either an aggregate function or the Group By clause"

Post the exact T-SQL of what you tried to pull off.

I see,

ORDER BY most_recent_contribution_date ASC

works.

Thanks again

select namecounter,sum(amount) as TotContributions,count(*) as NumofContributions
        ,sum(case when year(cndate) = 2012 then amount end) as 2012Contributions
        ,count(case when year(cndate) = 2012 then amount end) as 2012numofcontributions
        ,sum(case when year(cndate) = 2013 then amount end) as 2013contributions
        ,count(case when year(cndate) = 2013 then amount end) as 2013numofcontributions
        ,max(cndate) as lastcontributiondate
from db.mydb.cnhist
where cndate > '20111231' and cndate < '20140101'
group by namecounter
order by namecounter

Need to order by max(CNDate) since that  is what you're selecting.