SQL - How to sum up a column 'Balance' based on the first and last name of the records
I have a table
First|Last| Address|Bank Account|Balance
Tommy|Miller |1 Kingston St |123456|10.00
John |Smith|12 rose St |34567 | 20.00
John |Smith|12 rose St |91234 | 30.00
Tommy|Miller |1 Kingston St |23457 | 15.00
I am hopping I can do a Select that will give me such a result
Select First, Last, Sum(Balance) ......
Tommy|Miller |25.00
John |Smith| 50.00
Basically I want to report records of that has unique fullname (First + ' ' + Last)
I can use this below to get the unique records, but how can I modify to get the sum for each user.
with cte as
(
select First, Last, Address ,
ROW_NUMBER() OVER (PARTITION BY First, Last ORDER BY First, Last) RN
from TestDuplicate.dbo.Contact
)
select First, Last, Address
from cte
where RN = 1
First|Last| Address|Bank Account|Balance
Tommy|Miller |1 Kingston St |123456|10.00
John |Smith|12 rose St |34567 | 20.00
John |Smith|12 rose St |91234 | 30.00
Tommy|Miller |1 Kingston St |23457 | 15.00
I am hopping I can do a Select that will give me such a result
Select First, Last, Sum(Balance) ......
Tommy|Miller |25.00
John |Smith| 50.00
Basically I want to report records of that has unique fullname (First + ' ' + Last)
I can use this below to get the unique records, but how can I modify to get the sum for each user.
with cte as
(
select First, Last, Address ,
ROW_NUMBER() OVER (PARTITION BY First, Last ORDER BY First, Last) RN
from TestDuplicate.dbo.Contact
)
select First, Last, Address
from cte
where RN = 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Patrick,
Thanks for your suggestion. I am working with a database from my supplier. I was not given any schema. But looking at it, it is not well structure.
Thanks for your suggestion. I am working with a database from my supplier. I was not given any schema. But looking at it, it is not well structure.
Ah yes, the dreaded problem of "Somebody Else's Data".
:)
:)
ASKER
Thanks
ASKER
When I add Address into the select statement
select First,Last, Address, sum(Balance) as totalbalance
from TestDuplicate.dbo.Contact
group by First,Last
I get an error when execute, how can I avoid this error, if I need the Address in my result
Column 'TestDuplicate.dbo.Contact