how to sum up

Question

how to sum up

vinay.varaala

Mr or Mrs. 500

Points: 576
More actions
September 11, 2012 at 5:36 am

#266465

select deliveryEntry.ID,Tank_Product as ProductName , sum(deliveryTank.grossVolume) as Total,
(sum(cost*(deliveryTank.grossVolume))) as TotalCash
from deliveryEntry
inner join deliveryMapping on deliveryEntry.ID=deliveryMapping.deliveryID
inner join deliveryTank on deliveryMapping.ID=deliveryTank.deliveryID AND (deliveryTank.deliveryID=(select MAX(deliveryMapping.ID) from deliveryMapping where deliveryMapping.deliveryID=50))
inner join Tanks on deliveryEntry.simmonsPID=Tanks.Simmons_PanelID
and Tanks.Tanks_ID=deliveryTank.tankID
inner join costTag on Tanks.Tank_Product=costTag.productType
where simmonsPID=5479 AND deliveryEntry.ID=50
group by Tank_Product,deliveryEntry.ID,cost
output :
id productname total totalcash
50UNLEADED6800.00 8500.0000
50DIESEL 10700.00 15515.0000
my question is I want to add up totalcash for id=50 is it possible?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

H4K Hall of Fame Points: 3374 More actions · Answer 1

output :
id productname total totalcash
50UNLEADED6800.00 8500.0000
50DIESEL 10700.00 15515.0000
my question is I want to add up totalcash for id=50 is it possible?

Can you explain what you want as output instead of shown above ?

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

vinay.varaala Mr or Mrs. 500 Points: 576 More actions · Answer 2

vinay.varaala

Mr or Mrs. 500

Points: 576

September 11, 2012 at 6:23 am

#1536187

i WANT TO COMBINE DIESEL AND UNLEADED PRODUCT TOTAL

H4K Hall of Fame Points: 3374 More actions · Answer 3

Then include only delivery_id in the select and group by clause.

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

vinay.varaala Mr or Mrs. 500 Points: 576 More actions · Answer 4

i TRIED BUT

i GOT THE SAME RESULT AS

8500.00

15515.00

--

i WANT TO SEE AS TOTAL BY COMBING TWO RESULT..

AS 25001.00

select sum(cost*(deliveryTank.grossVolume)) as Total

from deliveryEntry

inner join deliveryMapping on deliveryEntry.ID=deliveryMapping.deliveryID

inner join deliveryTank on deliveryMapping.ID=deliveryTank.deliveryID AND (deliveryTank.deliveryID=(select MAX(deliveryMapping.ID) from deliveryMapping where deliveryMapping.deliveryID=50))

inner join Tanks on deliveryEntry.simmonsPID=Tanks.Simmons_PanelID

and Tanks.Tanks_ID=deliveryTank.tankID

inner join costTag on Tanks.Tank_Product=costTag.productType

where simmonsPID=5479 AND deliveryEntry.ID=50

group by Tank_Product,deliveryEntry.ID,cost

H4K Hall of Fame Points: 3374 More actions · Answer 5

Use this instead:

select deliveryEntry.ID, sum(cost*(deliveryTank.grossVolume)) as Total

from deliveryEntry

inner join deliveryMapping on deliveryEntry.ID=deliveryMapping.deliveryID

inner join deliveryTank on deliveryMapping.ID=deliveryTank.deliveryID AND (deliveryTank.deliveryID=(select MAX(deliveryMapping.ID) from deliveryMapping where deliveryMapping.deliveryID=50))

inner join Tanks on deliveryEntry.simmonsPID=Tanks.Simmons_PanelID

and Tanks.Tanks_ID=deliveryTank.tankID

inner join costTag on Tanks.Tank_Product=costTag.productType

where simmonsPID=5479 AND deliveryEntry.ID=50

group by deliveryEntry.ID

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

vinay.varaala Mr or Mrs. 500 Points: 576 More actions · Answer 6

i GOT IT GROUP BY DILEVERYENTRY.SIMMONPANELID

THANKS FOR YOUR HELP

CHEERS.