SQL Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL Query

Author

Topic

iofred
Starting Member

5 Posts

Posted - 2009-02-18 : 07:42:42

Hi, first let me apologize in advance for being completely new to SQL, other then installing it. I have inherited the job for the regular being off ill, and am really really stuck.
Basically what I need is to run a query against a database (table), looking for the "message.size" for a total of 1300 users.
I have managed to get a query for the size of every message for one user, however am wondering if it would be possible to device a query which would provide me with a "total size" of the messages for all the users.

Already writing this does me head in, so any input would be much appreciated.

Your help much appreciated

Fred

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 07:46:16

We should be able to help. Can you post sample data, output, sql you wrote etc just so that we understand better please.

iofred
Starting Member

5 Posts

Posted - 2009-02-18 : 07:54:29

Hi there, the query used so far, which works for the user with ID100:
"select message.size as size from MESSAGE INNER JOIN ROUTE ON Message.MD5hashkey INNER JOIN EmailAddress ON Route.EmailID = EmailAddress.EmailID where EmailAddress.EmailID = 100"

Hope this makes more sense to you (than it does to me)

Fred

Your help much appreciated

Fred

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-18 : 07:54:44

somethiong like

select sum(col) from table
where .....

Madhivanan

Failing to plan is Planning to fail

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 08:03:08

It should look like this, Just fill in the missing join condition

select 
sum(message.[size]) as Size,EmailAddress.EmailID  
from MESSAGE 
INNER JOIN ROUTE ON Message.MD5hashkey --missing join condition
INNER JOIN EmailAddress ON Route.EmailID = EmailAddress.EmailID 
group by
EmailAddress.EmailID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-18 : 08:05:17

quote:
Originally posted by iofred

Hi there, the query used so far, which works for the user with ID100:
"select message.size as size from MESSAGE INNER JOIN ROUTE ON Message.MD5hashkey INNER JOIN EmailAddress ON Route.EmailID = EmailAddress.EmailID where EmailAddress.EmailID = 100"

Hope this makes more sense to you (than it does to me)

Fred

Your help much appreciated

Fred

I didn't see your reply when I posted my first reply

Madhivanan

Failing to plan is Planning to fail

iofred
Starting Member

5 Posts

Posted - 2009-02-18 : 08:14:32

Guys, that is really helpful, thanks for your input, and your patience.

How would I be able to group all the UserIDs though??

Not really looking forward to doing this 1300 times

Your help much appreciated

Fred

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-18 : 08:17:40

Do you have userid column?

select
sum(message.[size]) as Size,EmailAddress.Userid
from MESSAGE
INNER JOIN ROUTE ON Message.MD5hashkey --missing join condition
INNER JOIN EmailAddress ON Route.EmailID = EmailAddress.EmailID
group by
EmailAddress.Userid

Madhivanan

Failing to plan is Planning to fail

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 08:18:37

quote:
Originally posted by iofred

Guys, that is really helpful, thanks for your input, and your patience.

How would I be able to group all the UserIDs though??

Not really looking forward to doing this 1300 ties

Your help much appreciated

Fred

The query I posted should show you size for all email addresses satisfying the join conditions between the 3 tables. It needn't be run 1300 times.

iofred
Starting Member

5 Posts

Posted - 2009-02-18 : 09:36:11

okay, the first part of this was absolutely fantastic. The problem however is that I d not require every EmailAddress.EmailID entry, but only 1300 specific addresses.
How if I had a list of the email addresses, would I include a statement to make that work??

Sorry to be a pain, and sorry to be a noob (with only one braincell)

Your help much appreciated

Fred

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 09:39:58

quote:
Originally posted by iofred

okay, the first part of this was absolutely fantastic. The problem however is that I d not require every EmailAddress.EmailID entry, but only 1300 specific addresses.
How if I had a list of the email addresses, would I include a statement to make that work??

Sorry to be a pain, and sorry to be a noob (with only one braincell)

Your help much appreciated

Fred

where do you have information on this 1300 email addresses currently? in a table or file?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-18 : 09:54:47

maybe something like this

select
sum(message.[size]) as Size,EmailAddress.Userid
from MESSAGE
INNER JOIN ROUTE ON Message.MD5hashkey --missing join condition
INNER JOIN EmailAddress ON Route.EmailID = EmailAddress.EmailID AND EmailAddress.EmailId IN (SELECT your condition to choose 1300 EmailIds)
group by
EmailAddress.Userid

iofred
Starting Member

5 Posts

Posted - 2009-02-18 : 10:43:33

so if I have the email addresses in a file, would that be okay??
The problem would then probably be the relationship, wouldn't it??

Me ole head hurts, but everything you are doing is very much appreciated

Your help much appreciated

Fred

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 10:47:50

quote:
Originally posted by iofred

so if I have the email addresses in a file, would that be okay??
The problem would then probably be the relationship, wouldn't it??

Me ole head hurts, but everything you are doing is very much appreciated

Your help much appreciated

Fred

Can you post what your file looks like ? Just a bit from there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 11:40:40

quote:
Originally posted by iofred

so if I have the email addresses in a file, would that be okay??
The problem would then probably be the relationship, wouldn't it??

Me ole head hurts, but everything you are doing is very much appreciated

Your help much appreciated

Fred

then you need to use OPENROWSET to get data from file. see below

http://msdn.microsoft.com/en-us/library/ms190312.aspx

or use SSIS export import wizard and dump the file data to temp table and use like


select 
sum(message.[size]) as Size,EmailAddress.Userid 
from MESSAGE 
INNER JOIN ROUTE ON Message.MD5hashkey --missing join condition
INNER JOIN EmailAddress ON Route.EmailID = EmailAddress.EmailID 
INNER JOIN #temp t ON  EmailAddress.EmailId =t.EmailId
group by
EmailAddress.Userid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources