MIN and SUM in one - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

MIN and SUM in one

Author

Topic

SQLBUNNY
Starting Member

3 Posts

Posted - 2015-02-20 : 05:40:31

Hi Guys and Girls,
I'm new here and I was hoping you could help me. My IT Department couldn't do it, and obviously I couldn't do it...so here it goes...

I have a table that Looks like this:

ID Date Amount Canceled
1 23.06.2008 10 0
1 04.07.2009 34 0
1 05.06.2012 26 0
1 01.03.2014 38 0
2 05.05.2014 8 0
2 05.09.2014 9 0
2 03.01.2015 66 0
2 04.02.2015 8 0
23 04.04.2014 98 0
3 02.03.2013 975 0
3 02.02.2015 83 0
3 05.02.2015 82 0
4 04.01.2014 92 0
4 05.06.2014 8372 0
4 09.09.2014 82 0
4 03.02.2015 334 0
46 02.01.2014 44 0
6 06.03.2014 55 0
6 08.08.2014 21 0
6 04.02.2015 25 0
6 05.02.2015 46 0
7 01.06.2014 77 0
7 18.09.2014 98 0
9 03.03.2014 7 0
9 04.02.2015 34 0
17 04.07.2014 20 1
17 12.09.2014 23 1
17 02.02.2015 100 0

What I need as a result should look like this:

ID FirstDate Count Sum2014
2 2014 2 17
23 2014 1 98
4 2014 3 8546
46 2014 1 44
6 2014 2 76
7 2014 2 175
9 2014 1 7

Meaning: I want all customers (Grouped by ID) who made their first payment in the year 2014 and then I want the amount (sum)and count of the payments - but only for the year 2014. Excluding all canceled payments (canceled =1).

I got a far as the IDs with their first payment in 2014 by using MIN on the date, but when I added a sum and count I also got the payment amounts from 2015 in my sum. Do I need a subselect? What do I need?

I hope you get what I mean. Can you help?

Thank you all already.

SQLBUNNY

No is not an Option!!!

mhorseman
Starting Member

44 Posts

Posted - 2015-02-20 : 06:03:03

Welcome to SQLTeam!

See if the below works for you. The first part is setting up some data so we can test code against it.

--set up
set dateformat dmy
drop table #t
create table #t (id int,paydate datetime,amt int,cancelled bit)

insert into #t
select 1,'23/06/2008',10,0 union all
select 1,'04/07/2009',34,0 union all
select 1,'05/06/2012',26,0 union all
select 1,'01/03/2014',38,0 union all
select 2,'05/05/2014',8,0 union all
select 2,'05/09/2014',9,0 union all
select 2,'03/01/2015',66,0 union all
select 2,'04/02/2015',8,0 union all
select 23,'04/04/2014',98,0 union all
select 3,'02/03/2013',975,0 union all
select 3,'02/02/2015',83,0 union all
select 3,'05/02/2015',82,0 union all
select 4,'04/01/2014',92,0 union all
select 4,'05/06/2014',8372,0 union all
select 4,'09/09/2014',82,0 union all
select 4,'03/02/2015',334,0 union all
select 46,'02/01/2014',44,0 union all
select 6,'06/03/2014',55,0 union all
select 6,'08/08/2014',21,0 union all
select 6,'04/02/2015',25,0 union all
select 6,'05/02/2015',46,0 union all
select 7,'01/06/2014',77,0 union all
select 7,'18/09/2014',98,0 union all
select 9,'03/03/2014',7,0 union all
select 9,'04/02/2015',34,0 union all
select 17,'04/07/2014',20,1 union all
select 17,'12/09/2014',23,1 union all
select 17,'02/02/2015',100,0
--

--Run code
select ID,MIN(paydate),COUNT(*),SUM(case when year(paydate) = 2014 then amt end)
from #t
where cancelled = 0
group by id
having year(MIN(paydate)) = 2014

Mark

SQLBUNNY
Starting Member

3 Posts

Posted - 2015-02-20 : 06:16:18

Hi. Wow. Thank you. That was really quick. I should have mentioned that I have "read only" rights on those tables. I can't create tables or views or anything. Does that Statement still work if I use the original table (payments)?

I guess I should go an try it

I will let you know. Thank you again!!!

No is not an Option!!!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-02-20 : 11:06:26

[code]
SELECT
t.ID,
MIN(t.Date) AS FirstDate,
SUM(CASE WHEN t.Date >= '20140101' AND t.Date < '20150101' THEN 1 ELSE 0 END) AS Count2014,
SUM(CASE WHEN t.Date >= '20140101' AND t.Date < '20150101' THEN t.Amt ELSE 0 END) AS Sum2014
FROM #t t
WHERE
t.cancelled = 0
GROUP BY
t.ID
HAVING
MIN(Date) >= '20140101' AND
MIN(Date) < '20150101'
ORDER BY
t.ID

[/code]

SQLBUNNY
Starting Member

3 Posts

Posted - 2015-02-22 : 05:15:34

I tried it and it worked perfectly.

Thank you so much ScottPletcher and mhorseman!!!

There are a lot more Problems where that came from - so I bet you will hear from me again soon

No is not an Option!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources