Solved

List Family only once

Posted on 2013-11-20
12
243 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:garysamuels
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:garysamuels
ID: 39662805
Sum would be great
0
 
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:garysamuels
ID: 39662829
The last date would be good. This would tell me the most recent contribution date.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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:garysamuels
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:garysamuels
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 31

Expert Comment

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

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 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.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now