MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > MS SQL Development
Reload this Page

Trouble with sum() function

Discuss Trouble with sum() function in the MS SQL Development forum on Dev Shed.
Trouble with sum() function MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Generate data entry and reporting .NET Web apps in minutes, straight from your database. Read our FREE whitepaper “Build Web 2.0 Applications Without Hand-Coding” Download now!
  #1  
Old August 20th, 2004, 03:51 PM
devikta devikta is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2004
Location: Houston,TX
Posts: 3 devikta User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via Yahoo to devikta
Trouble with sum() function
I have a pretty nasty situation in where I have two tables that hold quantity values for an interval of time. One table has an hourly date format while the other is every 5 minutes. I'm using the below t-sql to pull back the summed quantities I need in an HOURLY format; however, it is doubling the MarketQty field becuase it's not summing the qty field from the HE (henwood_extract) table correctly:

Here's a sample of what I'm trying to do.....

imo_meter_data table has records like this

Code:
ID    END_DATE             QTY
2     2004/08/18 00:05   2
2     2004/08/18 00:10   2.987
2     2004/08/18 00:15   3.1


henwood_extract is like this

Code:
ID    END_DT                QTY
132  2004/08/18 00:00  6.087
133  2004/08/18 00:00  1


I'm building a comparison tool, so I want the data from the query to return like this:

Code:
MyFDate                MarketQty     HenwoodQty
2004/08/18 00:00    7.087           7.087


Which would be correct if the sums worked in the query, but what it's looking like is this:

Code:
MyFDate                MarketQty     HenwoodQty
2004/08/18 00:00    14.174          14.174


Here's the query that I'm using:
Code:
select  imo.meter_id, Convert(datetime, cast(datepart(mm, imo.end_date) as varchar) 
	+ '/' + cast(datepart(dd, imo.end_date) as varchar)
	+ '/' + cast(datepart(yyyy, imo.end_date) as varchar) 
	+ ' ' + cast(datepart(hh, imo.end_date) as varchar)
	+ ':00', 120) MyFDate, 
	sum(imo.qty) MarketQty, sum(he.qty) as HenwoodQty
from imo_meter_data imo, henwood_extract he where 
	convert(datetime, imo.end_date, 120) between 
		convert(datetime, '08/18/2004', 120) and 
		convert(datetime, '08/19/2004', 120) and 
	convert(datetime, he.end_dt, 120) between 
		convert(datetime, '08/18/2004', 120) and 
		convert(datetime, '08/19/2004', 120) and 
	convert(datetime, he.end_dt, 120) = Convert(datetime, cast(datepart(mm, imo.end_date) as varchar) 
		+ '/' + cast(datepart(dd, imo.end_date) as varchar)
		+ '/' + cast(datepart(yyyy, imo.end_date) as varchar) 
		+ ' ' + cast(datepart(hh, imo.end_date) as varchar)
		+ ':00', 120) and 
	he.plant_name = 'Brighton Beach' and
	he.meter_type = 'ENERGY'
group by imo.meter_id, convert(datetime, cast(datepart(mm, imo.end_date) as varchar) 
	+ '/' + cast(datepart(dd, imo.end_date) as varchar)
	+ '/' + cast(datepart(yyyy, imo.end_date) as varchar) 
	+ ' ' + cast(datepart(hh, imo.end_date) as varchar)
	+ ':00', 120)
order by MyFDate


Any and all help is greatly appreciated.
Reply With Quote
  #2  
Old August 22nd, 2004, 06:47 AM
swampBoogie swampBoogie is offline
Contributing User
Dev Shed Intermediate (1500 - 1999 posts)
  
Join Date: Jan 2003
Location: Paris Uppland
Posts: 1,766 swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 2 Weeks 4 Days 6 h 12 m 39 sec
Reputation Power: 37
You shouldn't join the tables. Rather something like

Code:
select  imo.meter_id, Convert(datetime, cast(datepart(mm, imo.end_date) as varchar) 
	+ '/' + cast(datepart(dd, imo.end_date) as varchar)
	+ '/' + cast(datepart(yyyy, imo.end_date) as varchar) 
	+ ' ' + cast(datepart(hh, imo.end_date) as varchar)
	+ ':00', 120) MyFDate, 
	sum(imo.qty) MarketQty, 
(select sum(he.qty) from henwood_extract he
where convert(datetime, he.end_dt, 120) between 
		convert(datetime, '08/18/2004', 120) and 
		convert(datetime, '08/19/2004', 120) 
	he.plant_name = 'Brighton Beach' and
	he.meter_type = 'ENERGY')
as HenwoodQty
from imo_meter_data imo
 where 	convert(datetime, imo.end_date, 120) between 
		convert(datetime, '08/18/2004', 120) and 
		convert(datetime, '08/19/2004', 120) and
	convert(datetime, he.end_dt, 120) = Convert(datetime, cast(datepart(mm, imo.end_date) as varchar) 
		+ '/' + cast(datepart(dd, imo.end_date) as varchar)
		+ '/' + cast(datepart(yyyy, imo.end_date) as varchar) 
		+ ' ' + cast(datepart(hh, imo.end_date) as varchar)
		+ ':00', 120) and 
group by imo.meter_id, convert(datetime, cast(datepart(mm, imo.end_date) as varchar) 
	+ '/' + cast(datepart(dd, imo.end_date) as varchar)
	+ '/' + cast(datepart(yyyy, imo.end_date) as varchar) 
	+ ' ' + cast(datepart(hh, imo.end_date) as varchar)
	+ ':00', 120)
order by MyFDate
Reply With Quote
  #3  
Old August 23rd, 2004, 08:36 AM
devikta devikta is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2004
Location: Houston,TX
Posts: 3 devikta User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via Yahoo to devikta
This statement doesn't work. I need it to wrap up the values and report them in an hourly format. The subselect just sums up the quantity field for all rows that fall within the query parameters.


Quote:
Originally Posted by swampBoogie
You shouldn't join the tables. Rather something like

Code:
select  imo.meter_id, Convert(datetime, cast(datepart(mm, imo.end_date) as varchar) 
	+ '/' + cast(datepart(dd, imo.end_date) as varchar)
	+ '/' + cast(datepart(yyyy, imo.end_date) as varchar) 
	+ ' ' + cast(datepart(hh, imo.end_date) as varchar)
	+ ':00', 120) MyFDate, 
	sum(imo.qty) MarketQty, 
(select sum(he.qty) from henwood_extract he
where convert(datetime, he.end_dt, 120) between 
		convert(datetime, '08/18/2004', 120) and 
		convert(datetime, '08/19/2004', 120) 
	he.plant_name = 'Brighton Beach' and
	he.meter_type = 'ENERGY')
as HenwoodQty
from imo_meter_data imo
 where 	convert(datetime, imo.end_date, 120) between 
		convert(datetime, '08/18/2004', 120) and 
		convert(datetime, '08/19/2004', 120) and
	convert(datetime, he.end_dt, 120) = Convert(datetime, cast(datepart(mm, imo.end_date) as varchar) 
		+ '/' + cast(datepart(dd, imo.end_date) as varchar)
		+ '/' + cast(datepart(yyyy, imo.end_date) as varchar) 
		+ ' ' + cast(datepart(hh, imo.end_date) as varchar)
		+ ':00', 120) and 
group by imo.meter_id, convert(datetime, cast(datepart(mm, imo.end_date) as varchar) 
	+ '/' + cast(datepart(dd, imo.end_date) as varchar)
	+ '/' + cast(datepart(yyyy, imo.end_date) as varchar) 
	+ ' ' + cast(datepart(hh, imo.end_date) as varchar)
	+ ':00', 120)
order by MyFDate
Reply With Quote
  #4  
Old August 25th, 2004, 05:03 PM
Blase Blase is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2004
Posts: 11 Blase User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Could you provide more information on the relation between these tables? You do need a join (and all that juggling with cast and convert is not it). Also, looking at the 'ID' data for henwood_extract, it appears that the sum you are expecting is not exactly correctly, considering those are two different ID records.

My advice would be to review the relation between the tables and join them first (preferably using the INNER JOIN syntax) then attempt to match the date fields within a range.

-Blasé
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > MS SQL Development > Trouble with sum() function

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway.