I need help in how to use SUM selection.

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

General SQL Server Forums

New to SQL Server Programming

I need help in how to use SUM selection.

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-11-27 : 15:13:07

Hi Team:

This is a new query and I am facing a new problem. Here I am requesting all the records for a Contract Number 'GTH00001' for the specific date of 2/2/2007. I want the SUM(c.rcpt_nom_vol)AS Receipt and the b]SUM(c.rcpt_fuel)As Allocated_Fuel[/b]
But Instead I am getting 147 records for Contract Number'GTH00001'for the date of 2/2/2007.


Declare @Req_Contract_nbr char (8),
@Req_Day int,
@Req_month int,
@Req_year  int

Set @Req_Contract_nbr  = 'GTH00001'
Set @Req_Day  = '2'
set @Req_month = '2'
Set @Req_year ='2007'

SELECT Distinct a.contract_nbr,  
c.beg_eff_date, c.rcpt_dlvry_ind, 
SUM(c.rcpt_nom_vol)AS Receipt, SUM(c.rcpt_fuel)As Allocated_Fuel
from dbo.contract a
Inner Join dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date) 
and  (c.rcpt_dlvry_ind ='R') and a.contract_nbr= @Req_Contract_nbr 
and Day(c.Beg_eff_date) =  @Req_Day 
and (month(c.Beg_eff_date)=  @Req_month )
Group by a.contract_nbr,c.beg_eff_date, c.rcpt_dlvry_ind, c.rcpt_nom_vol, c.rcpt_fuel

Data Example


Contract_nbr    specific Date              rcpt_nom_vol rcpt_fuel
GTH00001	2007-02-02 00:00:00.000	R	4.0	0.0
GTH00001	2007-02-02 00:00:00.000	R	8.0	0.0
GTH00001	2007-02-02 00:00:00.000	R	11.0	0.0
GTH00001	2007-02-02 00:00:00.000	R	12.0	1.0
GTH00001	2007-02-02 00:00:00.000	R	14.0	1.0
GTH00001	2007-02-02 00:00:00.000	R	15.0	1.0
GTH00001	2007-02-02 00:00:00.000	R	16.0	1.0
GTH00001	2007-02-02 00:00:00.000	R	16.0	2.0
GTH00001	2007-02-02 00:00:00.000	R	16.0	4.0

Thank you for all your help!!!!!!!!! I deeply appreciate all your help.

Osiris
The future depends of what we do in the present- Mahatma Gandhi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-27 : 15:21:43

That's because you are grouping the data. Try this instead:


SELECT SUM(c.rcpt_nom_vol)AS Receipt, SUM(c.rcpt_fuel)As Allocated_Fuel
from dbo.contract a
Inner Join dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date) 
and  (c.rcpt_dlvry_ind ='R') and a.contract_nbr= @Req_Contract_nbr 
and Day(c.Beg_eff_date) =  @Req_Day 
and (month(c.Beg_eff_date)=  @Req_month )

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-11-27 : 15:25:32

Thank you Tara Kizer :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources