?
Solved

List Family only once

Posted on 2013-11-20
12
Medium Priority
?
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 25

Expert Comment

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

SELECT Distinct [NameCounter]
0
 
LVL 66

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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 66

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 66

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 66

Accepted Solution

by:
Jim Horn earned 2000 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 66

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

Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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 part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

800 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