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.
| Author |
Topic |
|
nikoz
Yak Posting Veteran
63 Posts |
 Posted - 2015-02-04 : 08:26:25
|
| I have 2 tables t1KEY | DATE|141 | 2014-01-01 | |142 | 2014-02-15 | |143 | 2014-04-17 ||151 | 2015-06-04 ||142 | 2015-03-03 | t2KEY IDENT AMOUNT141 | 21 | 5 |141 | 22 | 7 | 142 | 23 | 3 |143 | 21 | 2 | 143 | 22 | 8 |143 | 23 | 9 |151 | 24 | 6 |152 | 21 | 1 | How to get max of column KEY and SUM column AMOUNT for each IDENTThe result should look like thisDATE KEY IDENT AMOUNT2014-04-17 | 143 | 22 | 8 |2014-04-17 | 143 | 23 | 9 |2015-06-04 | 151 | 24 | 6 |2015-03-03 | 152 | 21 | 1 | |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-02-04 : 09:22:25
|
| There are problems with your data,in t2, you have a key=152. However there is no 152 in t1. So, where are you getting the date that matches key 152? |
 |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2015-02-04 : 10:06:24
|
| My mistakeI have 2 tables t1KEY | DATE|141 | 2014-01-01 | |142 | 2014-02-15 | |143 | 2014-04-17 ||151 | 2015-06-04 ||152 | 2015-03-03 | t2KEY IDENT AMOUNT141 | 21 | 5 |141 | 22 | 7 | 142 | 23 | 3 |143 | 21 | 2 | 143 | 22 | 8 |143 | 23 | 9 |151 | 24 | 6 |152 | 21 | 1 | How to get max of column KEY and SUM column AMOUNT for each IDENTThe result should look like thisDATE KEY IDENT AMOUNT2014-04-17 | 143 | 22 | 8 |2014-04-17 | 143 | 23 | 9 |2015-06-04 | 151 | 24 | 6 |2015-03-03 | 152 | 21 | 1 | |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-02-04 : 10:27:27
|
| OK -- now I wonder about your desired results. e.g. ident 21 has a total amount of 5+2+1 = 8 and a max key = 152. But, why do you want the total amount to be just 1? That is NOT the total amount for the ident |
|
|
|
|
|
|
MAX value from 2 joined table and SUM