We help IT Professionals succeed at work.

How to create an aggregage table based on month/year

mdewstowe99
mdewstowe99 used Ask the Experts™
on
I have 20 million records, each has a date.  About 50,000 exist on each day.
I want to be able to create an aggregate table based on the date.  It needs to be aggregated into month/year.

As I am not hugely famialiar with SQL yet, it would be great to understand if this can be done in the query designer.  If not, suggestions on the SQL are most welcome.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer
Commented:
select datefield
from yourTable
group by datefield


you can apply any aggregation function on the above query , like max() or min() , for example:
select datefield, max(field1), min(field1), count(*)
from yourTable
group by datefield



Expert of the Quarter 2010
Expert of the Year 2010
Commented:
For a one off, a simple query will do.
For it to be aggregated on an ongoing basis... let us know if this is the requirement.
If it is fast enough, you could run the one-off query every 2 hours or so and rebuild the table.

Select
year(datefield) as TheYear,
month(datefield) as TheMonth,
convert(datetime, convert(char(8),year(datefield)*10000+month(datefield)*100+1)) as FirstDayOfMonth,
sum(valuecolumn) TotalValueOfMonth,
count(valuecolumn) CountInMonth
group by year(datefield), month(datefield)

Something like this?
Having year, month and first day of month allows you to use the aggregated table in various ways quickly with the right index, e.g.

year on year comparison by month (using index on year+month)
5-month rolling average analysis (using index on FirstDayOfMonth and date functions)
Top Expert 2011

Commented:
20 million rows isn't necessarily that large...

what sort of groupings do you want to apply?
(apart from year month)
e.g product, agent, source, geographic location...  

by all means add year and month columns to the data if that is going to be your prime level of reporting

but a standard index on the "date" column will probably suffice..

you may find it's as cheap to just create a view on the table to display and query the data, rather than
actually creating a physical summary table, particularly if you then have to worry about creating routines to maintain it....
Top Expert 2011

Commented:
additionally

creating a "calendar" dimension table which associates dates with all your financial/reporting requirements can be a good investment... that way you have a standard definition for first quarter, financial year, tax year, you can also indicate public holidays etc to assess the impact of those on sales etc....

from a warehouse/analysis/reporting point of view its often then better to add those values to the base
fact table so that when reporting unnecessary joining of data is minimised....


hth

Author

Commented:
Great. Can this be done in the query designer or do I need to write the SQL. As you might sense I am new to SQL.

Author

Commented:
Great. Can this be done in the query designer or do I need to write the SQL. As you might sense I am new to SQL.

Author

Commented:
I have a database of jobs advertised on the internet.
Each has a date found, category, source, region and some other fields. I definitely want to roll up by year and then month. Does it matter then in what order I group the other fields? Does it matter if I group by source and then category or category and source?

Or is it more important to ensure the indices are right.

Author

Commented:
How can I aggergate by the first day of the month, so counts are  based on 1st Jan 2010, 1st Feb 2010 etc.
I need to have one continuous aggregation on date rather than seperating into years and months.
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Way up to http:#a33129287 builds a table you can use with your other question.
It completely rebuilds the table and is based on first day of month.
Additionally, it has year/month columns separated out for bonus usage, but not required if you don't need them.

Author

Commented:
Lovely.

Author

Commented:
If, once a table is created, and then truncated and insert is used, how can one update the structure of the table without recreating it? Say the table is formed from a view. And I add a new field to the view. Do I need to create a new table or can I also insert this new field? This way existing indices are maintained.
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
When you add a field to the view add it also to your table that is the target of the view.  One for one, it doesn't even have to have any value to begin with; the next refresh will put data into the new field.

Author

Commented:
Again my thanks!!
Easy when you know how n
Steve WalesSenior Database Administrator

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.