helping adding on to 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

SQL Server 2000 Forums

Transact-SQL (2000)

helping adding on to query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-11-06 : 01:55:28

Hi,

I have the following query, which is running fine however I'd like to add a bit of functionality on to it. This selects the current INBOUND for each user. I want to add 2 more columns, OUTBOUND which is generated the same way with a JOIN onto IM.messageFromID instead of IM.messageToID. The total could be derived from adding these two together, or doing a seperate query. The first idea is probably much more efficient. I'm having a tough time figuring this one out but I don't think it should really be that hard =
Thanks a bunch for any tips! I have included the SPROC below and table structure. Any questions please let me know

thanks once again,
mike123

CREATE procedure dbo.select_Most_InstantMessages_InBound

AS SET NOCOUNT ON

SELECT top 100 nameOnline, userID, COUNT(IM.instantMessageID) AS IMCount_Inbound
FROM tblUserDetails UD
INNER JOIN tblinstantmessage IM ON UD.userID = IM.messageToID
GROUP BY nameOnline, UD.userID
ORDER BY IMCount_Inbound desc

GO

Here is the table

CREATE TABLE [dbo].[tblInstantMessage] (
[InstantMessageID] [int] IDENTITY (1, 1) NOT NULL ,
[MessageToID] [int] NULL ,
[MessageFromID] [int] NULL ,
[Message] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [smalldatetime] NOT NULL ,
[Checked] [tinyint] NULL ,
[Mobile] [tinyint] NULL ,
[deletedbySender] [int] NULL ,
[deletedbyRecipient] [int] NULL
) ON [PRIMARY]
GO

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-06 : 02:12:47

[code]
CREATE procedure dbo.select_Most_InstantMessages_InBound
As
SELECT top 100 nameOnline, userID,
(
Select COUNT(IM.instantMessageID) From tblinstantmessage IM ON UD.userID = IM.messageToID
)AS IMCount_Inbound
(
Select COUNT(IM.messageFromID) From tblinstantmessage IM ON UD.userID = IM.messageFromID
)AS IMCount_outbound

FROM tblUserDetails UD
GROUP BY nameOnline, UD.userID
ORDER BY IMCount_Inbound desc
[/code]

Chirag

http://chirikworld.blogspot.com/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-11-06 : 02:44:25

Hi, Chiragkhabaria

thanks for the tip, I get this error when creating the SPROC. I think "JOIN" is supposed to be in there?

Also, I'd like to add a SUM(IMCount_outbound + IMCount_inbound) AS IMCount_total and then ORDER BY IMCount_total

thanks very much for your help!!
mike123

Server: Msg 156, Level 15, State 1, Procedure select_Most_InstantMessages_Total, Line 5
Incorrect syntax near the keyword 'ON'.
Server: Msg 156, Level 15, State 1, Procedure select_Most_InstantMessages_Total, Line 8
Incorrect syntax near the keyword 'ON'.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-06 : 02:51:14

opps try this


CREATE procedure dbo.select_Most_InstantMessages_InBound
As 
SELECT top 100 nameOnline, userID, 
(
	Select COUNT(IM.instantMessageID) From tblinstantmessage IM ON Where  UD.userID = IM.messageToID 
)AS IMCount_Inbound
(
	Select COUNT(IM.messageFromID) From tblinstantmessage IM  Where WUD.userID = IM.messageFromID  
)AS IMCount_outbound

FROM tblUserDetails UD
GROUP BY nameOnline, UD.userID
ORDER BY IMCount_Inbound desc

Chirag

http://chirikworld.blogspot.com/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-11-06 : 02:56:59

great thanks got it going .. do you know how I could add this one part I would like to on? I am having problems adding the total line I have bolded out.. if possible I'd also to ORDER BY this column

thanks once again :) much apprecaited!

mike123

alter procedure dbo.select_Most_InstantMessages_Total
As
SELECT top 100 nameOnline, userID,
(
Select COUNT(IM.instantMessageID) From tblinstantmessage IM Where UD.userID = IM.messageToID
)AS IMCount_Inbound,
(
Select COUNT(IM.messageFromID) From tblinstantmessage IM Where UD.userID = IM.messageFromID
)AS IMCount_outbound,

SUM(IMCount_Inbound + IMCount_OutBound) as IMCount_Total

FROM tblUserDetails UD
GROUP BY nameOnline, UD.userID
ORDER BY IMCount_Inbound desc

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-06 : 03:03:08

[code]
alter procedure dbo.select_Most_InstantMessages_Total
As
Select NameOnLine,UserID,IMCount_Inbound,IMCount_outbound,SUM(IMCount_Inbound + IMCount_OutBound) as IMCount_Total
From
(
SELECT top 100 nameOnline, userID,
(
Select COUNT(IM.instantMessageID) From tblinstantmessage IM Where UD.userID = IM.messageToID
)AS IMCount_Inbound,
(
Select COUNT(IM.messageFromID) From tblinstantmessage IM Where UD.userID = IM.messageFromID
)AS IMCount_outbound,
FROM tblUserDetails UD
GROUP BY nameOnline, UD.userID
) As a
Order by IMCount_Total

[/code]

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources