Loading Data Aggregated to Date into Fact Table

View previous topic View next topic Go down

Loading Data Aggregated to Date into Fact Table

Post  grahan007 on Tue May 26, 2009 9:12 am

I have following source table:
Source Table
ID App_Version Os_Version Date
9123305 2.5.2.60 Windows NT 5.1 2/15/2009
9123306 2.5.2.60 Windows NT 5.1 2/15/2009
9123307 2.5.2.60 Windows NT 5.1 2/15/2009
9123308 2.5.2.60 Windows NT 5.1 2/15/2009
9123309 2.5.2.60 Windows NT 6.0 2/15/2009
9123310 2.5.2.60 Windows NT 5.1 2/15/2009
9123311 2.5.2.60 Windows NT 5.1 2/15/2009
9123312 2.5.2.60 Windows NT 5.1 2/15/2009
9123313 2.5.2.60 Windows NT 6.0 2/15/2009
9123314 2.5.2.60 Windows NT 5.1 2/15/2009
9123315 2.5.2.60 Windows NT 5.1 2/15/2009
9123316 2.5.2.60 Windows NT 5.1 2/16/2009
9123317 2.5.2.60 Windows NT 5.1 2/17/2009
9123318 2.5.2.60 Windows NT 5.1 2/17/2009
9123319 2.5.2.60 Windows NT 5.1 2/17/2009
9123320 2.3.1.92 Windows NT 5.1 2/18/2009
9123321 2.5.2.60 Windows NT 5.1 2/18/2009
9123322 2.5.2.60 Windows NT 5.1 2/19/2009
9123323 2.5.2.60 Windows NT 5.1 2/19/2009
9123324 2.5.2.60 Windows NT 5.1 2/19/2009
9123325 2.5.2.60 Windows NT 5.1 2/19/2009
9123326 2.5.2.60 Windows NT 5.1 2/19/2009
9123327 2.5.2.60 Windows NT 5.1 2/20/2009
9123328 2.3.1.40 Intel Mac OS X 10.5 2/20/2009
9123329 2.5.2.60 Windows NT 6.0 2/20/2009

I want to insert aggregated data into my fact table which should look like following. It should aggregate the data per date per os_version per app_version and then count IDs for each group.

Sample Fact Table

Date OS_Version App_Version CountID
2/15/2009 Windows NT 5.1 2.5.2.60 9
2/15/2009 Windows NT 6.0 2.5.2.60 2
2/16/2009 Windows NT 5.1 2.5.2.60 1
2/17/2009 Windows NT 5.1 2.5.2.60 3
2/18/2009 Windows NT 5.1 2.5.2.60 1
2/18/2009 Windows NT 5.1 2.3.1.92 1
2/19/2009 Windows NT 5.1 2.5.2.60 5
2/20/2009 Windows NT 5.1 2.5.2.60 1
2/20/2009 Windows NT 6.0 2.5.2.60 1
2/20/2009 Intel Mac OS X 10.5 2.3.1.40 1

I am completely new to databases please guide me to solve this problem. It very urgent plz help.

Regards

Harris

grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

Re: Loading Data Aggregated to Date into Fact Table

Post  ngalemmo on Tue May 26, 2009 12:09 pm

INSERT INTO
SELECT ID, App_Version, Os_Version, Date, COUNT(*) CountID
FROM
GROUP BY ID, App_Version, Os_Version, Date;
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum