Adding values from two records

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 2005 Forums

Transact-SQL (2005)

Adding values from two records

Author

Topic

IanKM
Starting Member

18 Posts

Posted - 2011-06-02 : 06:51:46

Hi there

i have a table

custno Fare Fine
1 2 1
1 3 1.5
What i need to do is sum the two fines and the fares

can anyone help??

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-06-02 : 06:55:53

It is homework question.

Google for sum operator.

Vaibhav T

If I cant go back, I want to go fast...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-02 : 07:10:50

and group by clause probably.

What is the primary key on that table?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

IanKM
Starting Member

18 Posts

Posted - 2011-06-02 : 12:23:45

no this is not a homework question

SELECT P.DisplayName,sum(t.fare), sum(t.Debt) as debt
FROM Trip t
JOIN pax p ON t.paxid = p.paxid
WHERE t.bookingid = 82708
group by t.debt, p.displayname, t.Req_date, t.fare,

i'm using this bit of code to generate a report however its not adding the two fares that it will get and the two debts that it will get. thats what im trying to get it to do

if its not possible thats ok ill have to find another way around the problem.

oh and the primary key is the paxid

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-02 : 12:32:45

Just group by displayname. Your query says to make each combination of debt,displayname,req_date and fare an individual group (record), so nothing ends up getting aggregated.

Jim

Everyday I learn something that somebody else already knew

IanKM
Starting Member

18 Posts

Posted - 2011-06-03 : 04:49:51

hi there

thanks for your response, i altered the group by and it did add the columns. bu then if i added a text column that would be different in both rows it stopped adding

SELECT P.DisplayName, t.bookingid, t.Req_date, sum(t.fare), rc.description, sum(t.Debt) as debt, t.do_address
FROM Trip t
JOIN pax p ON t.paxid = p.paxid
JOIN refusecancel rc ON t.refusecancel = rc.code
WHERE t.bookingid = 82708
group by p.displayname, t.bookingid, t.Req_date, t.fare, rc.description, t.do_address

this is the bit of code that im actually using i put the other one to simplify things

t.do_address will be different in both cricumstances this stops the two sums being added

regards Ian

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 05:24:47

It doesn't stop the aggregation - it is aggregating for the rows in a group - you just have one row per group.
Having fare in the group by means that it will never aggreagte fare unless two rows have the same value - see my original post about the pk.

>> t.do_address will be different in both cricumstances this stops the two sums being added
Which address do you want in the group? If you want to group on the address then you will aggregate for that address.
If you want them both and the rest of the data duplicated for each address then that is possible too but a bit strange.

a possibility
SELECT P.DisplayName, t.bookingid, t.Req_date, sum(t.fare), description = max(rc.description), sum(t.Debt) as debt, do_address = max(t.do_address)
FROM Trip t
JOIN pax p ON t.paxid = p.paxid
JOIN refusecancel rc ON t.refusecancel = rc.code
WHERE t.bookingid = 82708
group by p.displayname, t.bookingid, t.Req_date

The problem is defining what you want rather than the coding.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

IanKM
Starting Member

18 Posts

Posted - 2011-06-14 : 06:13:05

That Worked a treat thanks alot Nigelrivett

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources