Aggregate Counting

Hi,

How can I aggregate a top 5 count across two satellite tables?

e.g. Orders and downloads table each have multiple entries for the same customer ID I would like to count the orders and add them to the downloads count too e.g. 5 orders added to 10 downloads giving 15 as the total for this customer and get a total 'site activity' result which I would like to select the top 5 for.

Any help or pointers would be a great help!

Thanks.What's the actual problem with doing this?|||Use subquerys:select Customers.CustomerID,
isnull(OrdersSubquery.OrderCount, 0) as Orders,
isnull(DownloadsSubquery.DownloadCount, 0) as Downloads,
isnull(OrdersSubquery.OrderCount, 0) + isnull(DownloadsSubquery.DownloadCount, 0) as Total
from Customers
left outer join
(select CustomerID,
count(*) as OrderCount
from Orders
group by CustomerID) OrdersSubquery
on Customers.CustomerID = OrdersSubquery.CustomerID
left outer join
(select CustomerID,
count(*) as DownloadCount
from Downloads
group by CustomerID) DownloadsSubquery
on Customers.CustomerID = DownloadsSubquery.CustomerID
...or count distinct, if you have pkeys on Orders and Downloads:select Customers.CustomerID,
count(distinct Orders.OrderID) as Orders,
count(distinct DownloadsOrderID) as Downloads,
count(distinct Orders.OrderID) + count(distinct DownloadsOrderID) as Total
from Customers
left outer join Orders on Customers.CustomerID = Orders.CustomerID
left outer join Downloads on Customers.CustomerID = Downloads.CustomerID
group by Customers.CustomerID|||Thanks for the advice, the top selection was ideal for my purposes!

I just had to 'customise' it a little and add a few more table counts etc and all's well

:)

agent mail / outlook 2003 catch 22

I've just installed and configured outlook 2003 with sql agent mail and I've come across a strange conundrum: messages from agent mail are not actually sent unless the outlook client is open, but the default mail folder is locked by agent when agent is started, thus preventing outlook 2003 from opening. If I try things the other way - opening the client and then configuring/restarting agent mail - then agent mail can't access the default mail folder because the client has it locked.

There doesn't seem to be any way to win here.

Anyone come across this issue and found a workaround?I don't have a solution for your specific problem. I use this:

http://sqldev.net/xp/xpsmtp.htm

which doesn't require an email client on the server at all. Perhaps it would work in your situation.|||Deleted duplicate post.|||If I recall correctly, you have to configure SQL Agent Mail using the Outlook 2000 Client.

Here are some links:

MS FAQ (http://support.microsoft.com/default.aspx?scid=kb;en-us;311231)
Common Problems (http://support.microsoft.com/default.aspx?scid=kb;en-us;315886)
Configuring SQL Mail (http://support.microsoft.com/default.aspx?scid=kb;en-us;263556)

Regards,

hmscott|||Thanks, guys. It appears that Outlook 2003 is not compatible with Agent Mail (though it's fine with SQL Mail).

For anyone else who runs into this problem, I've thrown together a stored procedure which, when scheduled into an hourly job, will email you when a job fails along with the cause of the failure. I'm sure I could have made this neater, but it works and that's all I need. An example of the error email message is as follows:

--Original Message--
From: sender@.mydomain.com
Sent: Tuesday, January 31, 2006 5:04 PM
To: me@.mydomain.com
Subject: Jobs failed in the last hour - Feb 01 2006 10:00AM

The jobs listed below failed in the last hour:

Job: testjob
Step: 1
Step Name: test
Date Failed: Feb 1 2006 9:31AM
Error: Executed as user: SERVERS\Administrator. Invalid object name 'zigglyzorp'. [SQLSTATE 42S02] (Error 208). The step failed.
--sp attached as txt file--

Advice on working across databases?

I have two databases, one for DATA and another for GIS data. Each customer
has a table which stores a 3 letter code. This code is different for all
customers. For each customer the GIS db is labeled like so (<3 letter
code>_GIS). I would like to create views that are stored in the DATA db, but
actually retrieves data from a table in the GIS db. The issue lies with
accessing the code for each individual customers.
Sample statement:
create view vw_Space
as
select ID,Vector1,Vector2 from <code>_GIS.dbo.tabelname
But I need to access the customer code from a table and concatenate that
into my select statement in my view. This way the views are dynamic, so to
speak. How can I accomplish this? If you need a better explanation please
contact me. ThanksTim,
Add in fully qualified names for your databases i.e.,
yourdatabase.yourowner.yourtable and try:
CREATE TABLE DATA
(CODE CHAR(3) NOT NULL)
CREATE TABLE GIS
(CODE CHAR(7) NOT NULL,
VECTOR1 INT NOT NULL,
VECTOR2 INT NOT NULL)
INSERT DATA
VALUES ('ABC')
INSERT DATA
VALUES ('DEF')
INSERT GIS
VALUES ('ABC_GIS', 1,2)
INSERT GIS
VALUES ('DEF_GIS', 1,2)
INSERT GIS
VALUES ('DEF_GIS', 3,4)
SELECT D.CODE, VECTOR1, VECTOR2
FROM DATA D JOIN GIS G
ON D.CODE = LEFT(G.CODE,3)
HTH
Jerry
"Tim Gains" <kixman@.excite.com> wrote in message
news:u$iOxWQyFHA.788@.tk2msftngp13.phx.gbl...
>I have two databases, one for DATA and another for GIS data. Each customer
>has a table which stores a 3 letter code. This code is different for all
>customers. For each customer the GIS db is labeled like so (<3 letter
>code>_GIS). I would like to create views that are stored in the DATA db,
>but actually retrieves data from a table in the GIS db. The issue lies with
>accessing the code for each individual customers.
> Sample statement:
> create view vw_Space
> as
> select ID,Vector1,Vector2 from <code>_GIS.dbo.tabelname
> But I need to access the customer code from a table and concatenate that
> into my select statement in my view. This way the views are dynamic, so to
> speak. How can I accomplish this? If you need a better explanation please
> contact me. Thanks
>
>|||thanks for the reply, but what I need to do is extract the code from the
users table, conctenate that in my views select statement, again this would
make the view dynamic if this can be done. I'm not able to ad any tables to
any of the DB's.
The table is called tblInstalation, this table houses the code field w/
only a single row and code is a field in the table. The following is wrong,
but will get my point across better perhaps.
create view vMyViewas
select first *
from (select code from tblInstalation) +_GIS .dbo.tblGISData
How and could this be accomplished in such a manner?
Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:et3QjiQyFHA.2848@.TK2MSFTNGP15.phx.gbl...
> Tim,
> Add in fully qualified names for your databases i.e.,
> yourdatabase.yourowner.yourtable and try:
> CREATE TABLE DATA
> (CODE CHAR(3) NOT NULL)
> CREATE TABLE GIS
> (CODE CHAR(7) NOT NULL,
> VECTOR1 INT NOT NULL,
> VECTOR2 INT NOT NULL)
> INSERT DATA
> VALUES ('ABC')
> INSERT DATA
> VALUES ('DEF')
> INSERT GIS
> VALUES ('ABC_GIS', 1,2)
> INSERT GIS
> VALUES ('DEF_GIS', 1,2)
> INSERT GIS
> VALUES ('DEF_GIS', 3,4)
> SELECT D.CODE, VECTOR1, VECTOR2
> FROM DATA D JOIN GIS G
> ON D.CODE = LEFT(G.CODE,3)
> HTH
> Jerry
> "Tim Gains" <kixman@.excite.com> wrote in message
> news:u$iOxWQyFHA.788@.tk2msftngp13.phx.gbl...
>|||Ok...I'm . The sample before used CREATE TABLE just so you would
have an working example for the query enclosed. >first * (first record?
by what criteria?) also the FROM is trying to "build" a database name >
code + _GIS?
Please advise.
HTH
Jerry
"Tim Gains" <papow@.yahoo.com> wrote in message
news:%23u03e1QyFHA.720@.TK2MSFTNGP15.phx.gbl...
> thanks for the reply, but what I need to do is extract the code from the
> users table, conctenate that in my views select statement, again this
> would make the view dynamic if this can be done. I'm not able to ad any
> tables to any of the DB's.
> The table is called tblInstalation, this table houses the code field w/
> only a single row and code is a field in the table. The following is
> wrong, but will get my point across better perhaps.
> create view vMyViewas
> select first *
> from (select code from tblInstalation) +_GIS .dbo.tblGISData
> How and could this be accomplished in such a manner?
> Thanks.
>
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:et3QjiQyFHA.2848@.TK2MSFTNGP15.phx.gbl...
>|||Jerry,
Bottom line I need to create a view in DB1 that will return data from a
table in another database (GIS). Here is an example:
create view vSubscriber
as
select first
, last
, startdate
, enddate
, subscriberid
, email
from ABC_GIS.dbo.subscriber
My question is how do I go about concatenating in my view, a field from a
DB1 table in my view. This field would be concatenated in the fully
qualified name portion of the select statement in the view. Again, each
customer has a 3 letter code that resides in his database, this code would
make up a portion of the GIS database name, say for instance ABC_GIS. This
code (ABC) would need to be extracted and concatenated into the views
statement.
create view vSubscriber
as
select first
, last
, startdate
, enddate
, subscriberid
, email
from <DB1.Field>_GIS.dbo.subscriber
ultimately would yield:
create view vSubscriber
as
select first
, last
, startdate
, enddate
, subscriberid
, email
from DB2.dbo.subscriber
Does that make more sense, not sure if it can be done,perhaps with an inline
function returning a table, your thought please.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23c0lT6QyFHA.428@.TK2MSFTNGP10.phx.gbl...
> Ok...I'm . The sample before used CREATE TABLE just so you would
> have an working example for the query enclosed. >first * (first record?
> by what criteria?) also the FROM is trying to "build" a database name >
> code + _GIS?
> Please advise.
> HTH
> Jerry
> "Tim Gains" <papow@.yahoo.com> wrote in message
> news:%23u03e1QyFHA.720@.TK2MSFTNGP15.phx.gbl...
>|||Tim,
I put it in a proc so you wouldn't have to create so many views.
Try:
CREATE PROC USP_SUBSCRIBER
@.CODE CHAR(3)
AS
DECLARE @.SQL VARCHAR(500)
SET @.SQL = 'SELECT first,last,startdate,enddate,subscriberi
d,email from ' +
@.CODE +'_GIS.dbo.subscriber'
EXEC(@.SQL)
EXEC USP_SUBSCRIBER 'ABC'
HTH
Jerry
"Tim Gains" <papow@.yahoo.com> wrote in message
news:O%23sPLIRyFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Jerry,
> Bottom line I need to create a view in DB1 that will return data from a
> table in another database (GIS). Here is an example:
> create view vSubscriber
> as
> select first
> , last
> , startdate
> , enddate
> , subscriberid
> , email
> from ABC_GIS.dbo.subscriber
> My question is how do I go about concatenating in my view, a field from a
> DB1 table in my view. This field would be concatenated in the fully
> qualified name portion of the select statement in the view. Again, each
> customer has a 3 letter code that resides in his database, this code would
> make up a portion of the GIS database name, say for instance ABC_GIS. This
> code (ABC) would need to be extracted and concatenated into the views
> statement.
>
> create view vSubscriber
> as
> select first
> , last
> , startdate
> , enddate
> , subscriberid
> , email
> from <DB1.Field>_GIS.dbo.subscriber
> ultimately would yield:
> create view vSubscriber
> as
> select first
> , last
> , startdate
> , enddate
> , subscriberid
> , email
> from DB2.dbo.subscriber
>
> Does that make more sense, not sure if it can be done,perhaps with an
> inline function returning a table, your thought please.
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23c0lT6QyFHA.428@.TK2MSFTNGP10.phx.gbl...
>|||Thanks, but I need these to be views, can this be done?
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uaCbVRRyFHA.3000@.TK2MSFTNGP12.phx.gbl...
> Tim,
> I put it in a proc so you wouldn't have to create so many views.
> Try:
> CREATE PROC USP_SUBSCRIBER
> @.CODE CHAR(3)
> AS
> DECLARE @.SQL VARCHAR(500)
> SET @.SQL = 'SELECT first,last,startdate,enddate,subscriberi
d,email from '
> + @.CODE +'_GIS.dbo.subscriber'
> EXEC(@.SQL)
> EXEC USP_SUBSCRIBER 'ABC'
> HTH
> Jerry
> "Tim Gains" <papow@.yahoo.com> wrote in message
> news:O%23sPLIRyFHA.1256@.TK2MSFTNGP09.phx.gbl...
>|||Basically I want these act as tables.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uaCbVRRyFHA.3000@.TK2MSFTNGP12.phx.gbl...
> Tim,
> I put it in a proc so you wouldn't have to create so many views.
> Try:
> CREATE PROC USP_SUBSCRIBER
> @.CODE CHAR(3)
> AS
> DECLARE @.SQL VARCHAR(500)
> SET @.SQL = 'SELECT first,last,startdate,enddate,subscriberi
d,email from '
> + @.CODE +'_GIS.dbo.subscriber'
> EXEC(@.SQL)
> EXEC USP_SUBSCRIBER 'ABC'
> HTH
> Jerry
> "Tim Gains" <papow@.yahoo.com> wrote in message
> news:O%23sPLIRyFHA.1256@.TK2MSFTNGP09.phx.gbl...
>|||and so that it could be parameterized.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uaCbVRRyFHA.3000@.TK2MSFTNGP12.phx.gbl...
> Tim,
> I put it in a proc so you wouldn't have to create so many views.
> Try:
> CREATE PROC USP_SUBSCRIBER
> @.CODE CHAR(3)
> AS
> DECLARE @.SQL VARCHAR(500)
> SET @.SQL = 'SELECT first,last,startdate,enddate,subscriberi
d,email from '
> + @.CODE +'_GIS.dbo.subscriber'
> EXEC(@.SQL)
> EXEC USP_SUBSCRIBER 'ABC'
> HTH
> Jerry
> "Tim Gains" <papow@.yahoo.com> wrote in message
> news:O%23sPLIRyFHA.1256@.TK2MSFTNGP09.phx.gbl...
>|||How do I know what value (3 letter code) you want used? Are you trying to
supply it when you query the view? Or are you just trying to create a bunch
of views using the codes from the other table as a one time op?
"Tim Gains" <papow@.yahoo.com> wrote in message
news:%23LVC$TRyFHA.3772@.TK2MSFTNGP11.phx.gbl...
> Basically I want these act as tables.
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uaCbVRRyFHA.3000@.TK2MSFTNGP12.phx.gbl...
>