Summing up while creating table?
-
Hi all,
When I import data using the import and export wizard, I use a create table SQL Statement. I am wondering if it's possible to sum up the rows during import?
For example, here is my sql statement
CREATE TABLE [Dbo].[Test] ([Full Name] nvarchar(255),
sum([Total Activities]) float
GROUP BY [Full Name]
)
In this case, in my data, I have
Full Name Total Activities
A 1
A 1
B 4
The ideal output would be:
Full Name Total Activities
A 2
B 4
-
No, the schema doesn't allow for that. What you'd either do is maintain it using an ETL process or you wrap the table with a view that would be called when you need the aggregates.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Thanks for the quick reply!
-
Alan Kwan (8/22/2012)
Thanks for the quick reply!Easy question with enough example to make sure I wasn't confused, the kind of question we all prefer. Keep 'em comin'. 😉 (Oh, and you're welcome. :-))
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Then is it possible to to write an SQL that will summarize the table into what I want? (permanately turning the table into a summarized table)
-
Alan Kwan (8/22/2012)
Then is it possible to to write an SQL that will summarize the table into what I want? (permanately turning the table into a summarized table)Are you familiar with views? What they are is encapsulated SQL that acts like a table but uses T-SQL language.
So, let's say you've got your BaseTable with your non-aggregated data.
You could then do something like:
CREATE VIEW aggBaseTable AS SELECT btID, SUM(value) AS sumValue FROM BaseTable GROUP BY btID
From there, you can then use that view as though it was a table, like:
SELECT sumValue FROM aggBaseTable WHERE btID = 2
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Cool, this might be the solution. Two questions,
1) I followed your sql, the command executed successfully, but I can't seem to find it, where is the view stored?
2) Is it possible to connect to this view from ms access?
-
Alan Kwan (8/22/2012)
Cool, this might be the solution. Two questions,1) I followed your sql, the command executed successfully, but I can't seem to find it, where is the view stored?
If you've got your object explorer up, it's in its own node:
2) Is it possible to connect to this view from ms access?
Yes, the MSDE will treat it as though it was a table. If this is the only 'real' access point for the data stored in the table make sure you use a clustered index with the leading edge on your most likely filter(s) and includes the rest of the 'grouping' in it.
Leading edge just means make sure they're first in your create index statement when you list out the columns. It ends up meaning more than that later but I'll let you research that if you care enough. 🙂
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
haha sorry I know this will be a stupid question. How do I create the view into a specific database?
Originally I had,
CREATE VIEW aggTest as...Then I managed to find this view in the master table under the system databases.
I want to create the view into the database OPADMIN, tried this,
CREATE VIEW [OPADMIN].[aggTest]...That didn't seem to work
-
Heheh, no worries.
USE OPAdminGO
CREATE VIEW aggTest AS ...
GO
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Wow Thank you, you saved my day! I never learned SQL from any courses or anything, this forum is where I learn most of my things, thanks teacher!
-
Couldn't you also do something like:
select cast([Full Name] as nvarchar(255)) as [Full Name],
cast(sum([Total Activities]) as float) as [Total Activities]
into [Test]
from where_ever_the_data_is
And then again, I might be wrong ...
David Webb -
@david-2: I agree that if you're warehousing and doing ETL work that you would prefer that approach to keep load down and keep it optimal, but I figured this isn't going to be that robust in the near future. KISS until otherwise required. You'd have to account for updatable data otherwise, and then we'd have to get into a reasonably deep design discussion.
@alan: My pleasure, see ya next time. 🙂
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
I agree, KISS until otherwise advised. The more complete solution you provided would be preferred.
And then again, I might be wrong ...
David Webb -
Alan Kwan (8/22/2012)
haha sorry I know this will be a stupid question. How do I create the view into a specific database?Originally I had,
CREATE VIEW aggTest as...Then I managed to find this view in the master table under the system databases.
I want to create the view into the database OPADMIN, tried this,
CREATE VIEW [OPADMIN].[aggTest]...That didn't seem to work
Seems the you are getting the solution for your college asisgnment 😉
BTW: Keep posting such questions!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply