Gary Samuels
asked on
List Family only once
In this query the [NameCounter] is a Family ID. The query returns many rows per family. How can I list a family only once.
I want to know if a family has made a contribution in 2012 or 2013
SELECT [NameCounter] ,[Amount],[CNDate]
FROM [DB].[MYDB].[CNHst]
WHERE [CNDate] > '2011-12-31' AND [CNDate] < '2014-01-01'
ORDER BY [NameCounter] ASC
I want to know if a family has made a contribution in 2012 or 2013
SELECT [NameCounter] ,[Amount],[CNDate]
FROM [DB].[MYDB].[CNHst]
WHERE [CNDate] > '2011-12-31' AND [CNDate] < '2014-01-01'
ORDER BY [NameCounter] ASC
>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?
>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?
ASKER
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?
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
ASKER
The last date would be good. This would tell me the most recent contribution date.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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"
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.
ASKER
I see,
ORDER BY most_recent_contribution_d ate ASC
works.
Thanks again
ORDER BY most_recent_contribution_d
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
,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.
SELECT Distinct [NameCounter]