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 |
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2014-01-08 : 22:52:01
|
i get the amount from table.select ID, Amount1 from tableAID Name Amount11 Mary 101 John 101 Jane 10select ID, Amount2 from tableAID Name Amount21 Mary 251 John 25then i combine it into one.ID Name Amount1 Amount21 Mary 10 251 John 10 251 Jane 10 0how can i combine into like this?NoOfPeople ID Amount1 Amount23 1 10 25 |
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2014-01-08 : 23:27:18
|
It's not clear why you get the numbers you do.- Why do they all have ID of 1 ? What's the PK?- Why do you have different # of rows in the queries to tableA?- What if there are different combinations of ID, name, amount etc? |
|
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2014-01-08 : 23:35:24
|
SELECT (SELECT COUNT(a.name)FROM Table1 AS a)AS 'NoOfPeople' , a.Id , Amount1 , Amount2 FROM Table1 AS a INNER JOIN Table2 AS b ON a.id=b.id GROUP BY a.Id,Amount1,Amount2veeranjaneyulu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-09 : 04:56:31
|
if they're all in same table this is enoughSELECT COUNT(1) OVER (PARTITION BY ID) AS NumberOfPeople,ID,Name,Amount1,Amount2FROM TableA ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|