Solved

List Family only once

Posted on 2013-11-20
12
252 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL GROUP BY 6 79
SQL QUERY 3 33
Applying Roles in Common Scenarios 3 19
denied execute as 13 31
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 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