Sum without duplicate result - 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 2000 Forums

Transact-SQL (2000)

Sum without duplicate result

Author

Topic

nizguy
Starting Member

37 Posts

Posted - 2012-07-24 : 13:38:30

Hello all,
I wrote this query and it return 2 rows.
How can i write this query return one row and the sum of the rp.Pallet_total in the middle column

Thanks

SELECT
r.RQuoteID, rp.Pallet_total, sub_amt + fuelSurcharge as 'RegQteAmt'
FROM
RQuote r inner join RQuote_PItem rp on r.RQuoteID = rp.RQuoteId
WHERE
r.RQuoteId = 1777336

Result
=======
1777336 1088.5800 1260.0000
1777336 173.2100 1260.0000

This is the output that I want
==============================
1777336 1261.79 1260.00

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-24 : 13:42:42

select RQuoteID, Pallet_total = sum(Pallet_total), RegQteAmt
frrom
(
SELECT r.RQuoteID, rp.Pallet_total, sub_amt + fuelSurcharge as RegQteAmt
FROM RQuote r
inner join RQuote_PItem rp
on r.RQuoteID = rp.RQuoteId
WHERE r.RQuoteId = 1777336
) a
group by RQuoteID, RegQteAmt

==========================================
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.

nizguy
Starting Member

37 Posts

Posted - 2012-07-24 : 14:12:36

Perfect.... Thank you
I have other questions.. Let say if I want return more columns, do i have to group by all these columns? If i want to display 20 columns then i have to group by 20 columns? does it slow down the process? Any alternate way to do this?
Thanks

select RQuoteID, Pallet_total = sum(Pallet_total), RegQteAmt, name, address1, city, state, zip
from
(
SELECT r.RQuoteID, rp.Pallet_total, sub_amt + fuelSurcharge as RegQteAmt
,name, address1, city, state, zip
FROM RQuote r
inner join RQuote_PItem rp
on r.RQuoteID = rp.RQuoteId
WHERE r.RQuoteId = 1777336
) a
group by RQuoteID, RegQteAmt, name, address1, city, state, zip

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 14:24:01

quote:
Originally posted by nizguy

Perfect.... Thank you
I have other questions.. Let say if I want return more columns, do i have to group by all these columns? If i want to display 20 columns then i have to group by 20 columns? does it slow down the process? Any alternate way to do this?
Thanks

select RQuoteID, Pallet_total = sum(Pallet_total), RegQteAmt, name, address1, city, state, zip
from
(
SELECT r.RQuoteID, rp.Pallet_total, sub_amt + fuelSurcharge as RegQteAmt
,name, address1, city, state, zip
FROM RQuote r
inner join RQuote_PItem rp
on r.RQuoteID = rp.RQuoteId
WHERE r.RQuoteId = 1777336
) a
group by RQuoteID, RegQteAmt, name, address1, city, state, zip

you cant do it inline

you need to do it via join


SELECT t.*,u.PalletTotal,u.RegQteAmt
FROM
(
SELECT r.RQuoteID, name, address1, city, state, zip
FROM RQuote r
inner join RQuote_PItem rp
on r.RQuoteID = rp.RQuoteId
WHERE r.RQuoteId = 1777336
)t
INNER JOIN
(
SELECT r.RQuoteID, SUM(rp.Pallet_total) AS PalletTotal, SUM(sub_amt + fuelSurcharge) as RegQteAmt
FROM RQuote r
inner join RQuote_PItem rp
on r.RQuoteID = rp.RQuoteId
WHERE r.RQuoteId = 1777336
GROUP BY r.RQuoteID
)u
ON u.RQuoteID = t.RQuoteID