Summing up data into a new table...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Summing up data into a new table...

Author

Topic

MrRay
Starting Member

28 Posts

Posted - 2006-10-13 : 16:37:43

Table "CallHistory"
===========================
DATE          TYPE   CALLS
===========================
2006/01/01    22     110
2006/01/01    33     101
2006/01/01    44     130
2006/01/02    22     142
2006/01/02    33     113
2006/01/02    44     141
2006/01/03    22     153
2006/01/03    33     126
2006/01/03    44     134
2006/01/04    22     146
2006/01/04    33     173
2006/01/04    44     168
2006/01/05    22     131
2006/01/05    33     183
2006/01/05    44     124

From the source data above (table "CallHistory"), I would like to create another table containing a record of the total number of calls per day of specific type(s) within a certain date range.  The parameters passed to the stored proc would be the DateFrom, DateTo (inclusive), and the types (1 or more).

Example:  From the source data, I want to create a new table called "RESULTS" containing the following:  A daily total of all calls from 2006/01/02 to 2006/01/04 (inclusive) that are of type 33 or 44.  My new table should contain:

Table "Results"
====================
DATE          TOTAL
====================
2006/01/02    254
2006/01/03    260
2006/01/04    341

I can create the new table, but I'm over-complicatig the select satement in the process of bringing this data into the new table, and I'm sure there's a simple thing I'm overlooking.

Can someone guide me in the right direction to get the correct Select statement going?

Thanks in advance,
Ben

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-13 : 17:26:21

Try the SELECT INTO syntax. Books Online is your friend.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

MrRay
Starting Member

28 Posts

Posted - 2006-10-13 : 18:18:54

quote:
Originally posted by blindman
Try the SELECT INTO syntax. Books Online is your friend.

Thanks, I appreciate that. However, my problem isn't getting the data into a new table. My problem is getting the data! I can't put together a SELECT statement (or SELECT INTO statement) that gives me the data I want...

Help!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-13 : 18:44:04

[code]
select
DATE,
TOTAL = sum(CALLS)
from
CallHistory
where
DATE between '200600102' and '20060104' and
TYPE in (33,44)
group by
DATE
order by
DATE
[/code]

CODO ERGO SUM

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-13 : 23:54:24

quote:
Originally posted by MrRay

quote:
Originally posted by blindman
Try the SELECT INTO syntax. Books Online is your friend.

Thanks, I appreciate that. However, my problem isn't getting the data into a new table. My problem is getting the data! I can't put together a SELECT statement (or SELECT INTO statement) that gives me the data I want...

Help!

Ok then...try the SELECT syntax.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

MrRay
Starting Member

28 Posts

Posted - 2006-10-14 : 15:14:24

quote:
Originally posted by blindman

quote:
Originally posted by MrRay

quote:
Originally posted by blindman
Try the SELECT INTO syntax. Books Online is your friend.

Thanks, I appreciate that. However, my problem isn't getting the data into a new table. My problem is getting the data! I can't put together a SELECT statement (or SELECT INTO statement) that gives me the data I want...

Help!
Ok then...try the SELECT syntax.

Wow, you have been very helpful. ;)

MrRay
Starting Member

28 Posts

Posted - 2006-10-14 : 15:18:06

quote:

Originally posted by Michael Valentine Jones


select
	DATE,
	TOTAL = sum(CALLS)
from
	CallHistory
where
	DATE between '200600102' and '20060104' and 
	TYPE in (33,44)
group by
	DATE
order by
	DATE

CODO ERGO SUM

Perfect, thanks Michael! I got close to that but I was having problems getting my head around how to use the group by. I appreciate your help.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-14 : 20:24:15

Now do yourself a favor, take my advice, and actually READ Books Online.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-15 : 02:20:56

If you are having trouble with this simple query, you need to spend some time learning SQL:
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

CODO ERGO SUM

MrRay
Starting Member

28 Posts

Posted - 2006-10-15 : 18:35:39

quote:
Originally posted by blindman

Now do yourself a favor, take my advice, and actually READ Books Online.

Of course I need to learn more - the fact that I posted such a "stupid" question in the first place should have made that clear!

When I want to dedicate a few hours to expanding my SQL knowledge, I'll use something like Books Online. However, in this particular situation, my limited SQL knowledge was sufficient - the problem is that I was over-complicating things, and overlooked the simple use of Group By. I was even to the point where I had some looping and dynamic SQL going!

I didn't post this question so that somebody would spoon-feed me a solution. I just needed someone to wake me up & say "Hey man, think about it... Use group by!" (Although I appreciate the code sample that I ended up expanding on.) And that's just what these types of forums are for.

Now I can finish this bloody time-critical project and move on to bigger & better things, one of which will likely be reading Books Online.

Thanks again.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-16 : 10:23:30

quote:
Originally posted by MrRay

I didn't post this question so that somebody would spoon-feed me a solution.

Well, spoon-feeding is what you got. Point is, this would have taken 10 minutes max to find out in Books Online.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

MrRay
Starting Member

28 Posts

Posted - 2006-10-16 : 13:15:20

quote:
Originally posted by blindman

quote:
Originally posted by MrRay

I didn't post this question so that somebody would spoon-feed me a solution.
Well, spoon-feeding is what you got. Point is, this would have taken 10 minutes max to find out in Books Online.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

I appreciate your concern. But I don't think you're getting what I'm saying here...

If I had been thinking clearly and knew that all I needed to do was use "Group By" statement, it would have taken seconds for me to get my statement working properly. I'm not new to Group By - I just wasn't thinking through my problem clearly, and was over-complicating it.

I had already ruled out Group By because, in some kind of code-all-day stupor, I thought my problem required a solution more complicated than Group By. I spent about an hour working up this ridiculous stored procedure with dynamic SQL, some kind of loop I'm not sure was even working, and then I thought "Wait, this is ridiculous. What am I really trying to accomplish here?"

And THAT is what this type of forum is for. If I had a co-worker sitting beside me, I would have asked him for some clarity. But I don't! So I presented my solution to some peers here, one of which reminded me that Group By was ALL I needed. I felt stupid, but that's what peers are for! ;)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-16 : 15:48:13

Okay then. We can agree on the purpose of peers.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-16 : 15:49:04

Wait a minute...I thought that was what SPOUSES were for?

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources