Solved

List Family only once

Posted on 2013-11-20
12
250 Views
Last Modified: 2013-11-20
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
0
Comment
Question by:Gary Samuels
12 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 39662606
Try playing around with the Distinct keyword

SELECT Distinct [NameCounter]
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39662792
>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?
0
 

Author Comment

by:Gary Samuels
ID: 39662805
Sum would be great
0
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 39662811
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?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39662823
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 

Open in new window

0
 

Author Comment

by:Gary Samuels
ID: 39662829
The last date would be good. This would tell me the most recent contribution date.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39662953
... adding last date ...
SELECT NameCounter, 
   Sum(Amount) as total_amount, 
   Max(CNDate) as most_recent_contribution_date
FROM CNHst
WHERE CNDate > '2011-12-31' AND CNDate < '2014-01-01'
GROUP BY NameCounter
ORDER BY NameCounter ASC 

Open in new window

btw I have an article out there on SQL Server GROUP BY Solutions if you're interested in a demo of what GROUP BY can do.
0
 

Author Comment

by:Gary Samuels
ID: 39663110
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"
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39663125
Post the exact T-SQL of what you tried to pull off.
0
 

Author Comment

by:Gary Samuels
ID: 39663128
I see,

ORDER BY most_recent_contribution_date ASC

works.

Thanks again
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39663140
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
0
 
LVL 32

Expert Comment

by:awking00
ID: 39663141
Need to order by max(CNDate) since that  is what you're selecting.
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 57
ms sql + get number in list out of total 7 36
RAISERROR WITH NOWAIT 2 16
question about results where i dont have a match 3 20
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 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