Solved

List Family only once

Posted on 2013-11-20
12
248 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

809 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