September 5, 2009 at 3:29 pm
Hi Guys,
I have a problem with a getting a sum statement to work as I want it to.
I have a table with columns: fromtime, totime, frequency, numberofvisits, hours
Example data:
10.00, 12.00, 3, ?, 2
10.00, 11.00, 1, ?, 1
10.00, 12.00, 1, ?, 2
15.00, 16.00, 2, ?, 1
Bascially I want to populate the numberofvisits column with the sum of the frequency dependent upon the number of hours
So the first row would have a number of visits = 4 (3+1) for the rows with 2 hours. Row 3 would have the same calclation. Rows 2 and 3 would have the number of visits as 3, (2+1)
Basically I need to calculate in the table how many 2 hour visits there are, how many 1 hour visits there are etc....
Hope this makes sense.
Matt
September 5, 2009 at 4:58 pm
I'm not following your description. Could you please draw a picture of the table as it should look with the rows calculated correctly?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 6, 2009 at 6:21 am
m.dunster (9/5/2009)
Bascially I want to populate the numberofvisits column with the sum of the frequency dependent upon the number of hoursSo the first row would have a number of visits = 4 (3+1) for the rows with 2 hours. Row 3 would have the same calclation. Rows 2 and 3 would have the number of visits as 3, (2+1)
Basically I need to calculate in the table how many 2 hour visits there are, how many 1 hour visits there are etc....
Hope this makes sense.
Matt
You seem to be describing three different things that you want the result to be.
So, based off of your last line:
Basically I need to calculate in the table how many 2 hour visits there are, how many 1 hour visits there are etc....
does this do what you're looking for?
declare @MyTable TABLE (
fromtime varchar(5),
totime varchar(5),
frequency int,
numberofvisits int,
[hours] int
)
insert into @MyTable (fromtime, totime, frequency, [hours])
select 10.00, 12.00, 3, 2 UNION ALL
select 10.00, 11.00, 1, 1 UNION ALL
select 10.00, 12.00, 1, 2 UNION ALL
select 15.00, 16.00, 2, 1
;with CTE AS
(
select [hours], numberofvisits = count(*)
from @MyTable
group by [hours]
)
update T
set T.numberofvisits = CTE.numberofvisits
from @MyTable T
INNER JOIN CTE ON T.[Hours] = CTE.[Hours]
select * from @MyTable
You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 6, 2009 at 6:37 am
That sounds more like an aggregate function...
I don't recommend to include aggregated values into the table structure you provided since it violates normalization (2NF, IIRC).
Attached please find a proposal using a slightly different table structure:
I didn't include the column [hours], since it can be calculated from totime-fromtime (at least based on your sample data). If required for performance reason, I'd use it as a derived column...
declare @t table (fromtime numeric(4,2), totime numeric(4,2), frequency int)
INSERT INTO @t
SELECT 10.00, 12.00, 3 UNION ALL
SELECT 10.00, 11.00, 1 UNION ALL
SELECT 10.00, 12.00, 1 UNION ALL
SELECT 15.00, 16.00, 2
SELECT *
FROM @t t INNER JOIN
(SELECT CAST(totime-fromtime AS INT) AS hours,
sum(frequency) AS numberofvisits
FROM @t
GROUP BY totime-fromtime
) f ON f.hours = (t.totime-t.fromtime)
/* result set:
fromtimetotimefrequencyhoursnumberofvisits
10.0012.00324
10.0011.00113
10.0012.00124
15.0016.00213
*/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply