help with query (count(*)) - 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 2005 Forums

Transact-SQL (2005)

help with query (count(*))

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-01-16 : 08:59:20

Hi,

I have the following query I would like to modify

currently it brings back 1 column called "OutBoundMessages_Duplicates"

I would like it to bring back 2 or 3 columns.

OutBoundMessages_Duplicates_total (or we can compute in web app)
OutBoundMessages_Duplicates_checked
OutBoundMessages_Duplicates_unchecked

The count is of the same column, it just depends on whether the column checked on tblInstantMessage is =0 or =1

I believe theres a better way to do this rather than a seperate count? Been playing around but can't get it.

Any help greatly appreciated!!

thanks again:),
mike123

(select count(messageFromID) FROM tblInstantMessage WHERE messageFromID = IM.messageFromID and message = IM.message) as OutBoundMessages_Duplicates,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-16 : 09:01:31

Where is some descriptive sample data?
You are getting some Italian habits by not providing appropriate information...

SELECT pkCol, COUNT(*), SUM(CASE WHEN Col1 = 1 THEN 1 ELSE 0 END) AS Task1,
SUM(CASE WHEN Col1 = 0 THEN 1 ELSE 0 END) AS Task2
FROM Table1
GROUP BY pkCol

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 09:01:38

[code]
SELECT SUM(CASE WHEN checked=0 THEN 1 ELSE 0 END)AS OutBoundMessages_Duplicates_unchecked,
SUM(CASE WHEN checked=1 THEN 1 ELSE 0 END)AS OutBoundMessages_Duplicates_checked,
COUNT(*) AS OutBoundMessages_Duplicates_total
FROM tblInstantMessage
[/code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-01-16 : 09:21:50

haha thanks Peso, sorry I thought it was actually pretty simple but I realize theres some complications. Trying to make things simpler made it harder which maybe I picked up here too ;)

Visakh16, appreciated too! 7 seconds apart things, very impressive :)

For integrating into this query, any advice on a better route?

CREATE PROCEDURE [dbo].[select_SPAM_report_IM]

AS SET NOCOUNT ON

SELECT TOP 1 spamReportID, offendingID, SR.spamTypeID, SRT.spamDescription, dateReported, reportedByUserID, UD.nameOnline as reportedByNameOnline,
IM.messageFromID, UD2.nameOnline as spamFromNameOnline, IM.message, IM.date, ISNULL(IM.IP, '') as FROM_IP, IM.checked, UD2.date as memberSince,

(select count(messageFromID) FROM tblInstantMessage WHERE messageFromID = IM.messageFromID) as OutBoundMessages,

(select count(messageFromID) FROM tblInstantMessage WHERE messageFromID = IM.messageFromID and message = IM.message) as OutBoundMessages_Duplicates,

(select count(spamReportID) FROM tblSpamReports SR2 JOIN tblInstantMessage IM2 ON SR2.offendingID = IM2.instantMessageID WHERE IM2.messageFromID = IM.messageFromID) as totalSpamComplaints, dateDiff(dd, UD2.date, getDate()) as membershipDays,

--OTHER TABLES
(select count(commentFromID) FROM tblComment WHERE commentFromID = IM.messageFromID) as OutBoundComments,
(select count(messageFrom) FROM tblMessage WHERE messageFrom = IM.messageFromID) as OutBoundMail,
(select count(commentFromID) FROM tblPhotoComments WHERE commentFromID = IM.messageFromID) as OutBoundPhotoComments

FROM tblSpamReports SR

JOIN tblUserDetails UD ON UD.userID = SR.reportedByUserID
JOIN tblSpamReports_Types SRT ON SR.spamTypeID = SRT.spamTypeID
JOIN tblInstantMessage IM ON IM.instantMessageID = SR.offendingID
JOIN tblUserDetails UD2 ON UD2.userID = IM.messageFromID

ORDER BY totalSpamComplaints desc

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 09:28:52

use APPLY operator.


CREATE PROCEDURE [dbo].[select_SPAM_report_IM]

AS SET NOCOUNT ON

SELECT TOP 1 spamReportID, offendingID, SR.spamTypeID, SRT.spamDescription, dateReported, reportedByUserID, UD.nameOnline as reportedByNameOnline,
IM.messageFromID, UD2.nameOnline as spamFromNameOnline, IM.message, IM.date, ISNULL(IM.IP, '') as FROM_IP, IM.checked, UD2.date as memberSince,

(select count(messageFromID) FROM tblInstantMessage WHERE messageFromID = IM.messageFromID) as OutBoundMessages,





(select count(spamReportID) FROM tblSpamReports SR2 JOIN tblInstantMessage IM2 ON SR2.offendingID = IM2.instantMessageID WHERE IM2.messageFromID = IM.messageFromID) as totalSpamComplaints, dateDiff(dd, UD2.date, getDate()) as membershipDays,

--OTHER TABLES
(select count(commentFromID) FROM tblComment WHERE commentFromID = IM.messageFromID) as OutBoundComments,
(select count(messageFrom) FROM tblMessage WHERE messageFrom = IM.messageFromID) as OutBoundMail,
(select count(commentFromID) FROM tblPhotoComments WHERE commentFromID = IM.messageFromID) as OutBoundPhotoComments,
 
tmp.utBoundMessages_Duplicates,
OutBoundMessages_Duplicates_unchecked,
OutBoundMessages_Duplicates_checked

FROM tblSpamReports SR

JOIN tblUserDetails UD ON UD.userID = SR.reportedByUserID
JOIN tblSpamReports_Types SRT ON SR.spamTypeID = SRT.spamTypeID
JOIN tblInstantMessage IM ON IM.instantMessageID = SR.offendingID
JOIN tblUserDetails UD2 ON UD2.userID = IM.messageFromID

OUTER APPLY (select count(messageFromID) as OutBoundMessages_Duplicates,
SUM(CASE WHEN checked=0 THEN 1 ELSE 0 END)AS OutBoundMessages_Duplicates_unchecked,
SUM(CASE WHEN checked=1 THEN 1 ELSE 0 END)AS OutBoundMessages_Duplicates_checked
 FROM tblInstantMessage WHERE messageFromID = IM.messageFromID and message = IM.message )tmp

ORDER BY totalSpamComplaints desc

similarly you can change other subqueries also

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-01-16 : 09:53:07

awesome thanks very much!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-16 : 09:56:41

Or use the new windowed functions availablein SQL Server 2005?

CREATE PROCEDURE [dbo].[select_SPAM_report_IM]
AS

SET NOCOUNT ON

SELECT TOP 1	sr.spamReportID,
		sr.offendingID,
		sr.spamTypeID,
		srt.spamDescription,
		sr.dateReported,
		sr.reportedByUserID,
		ud.nameOnline AS reportedByNameOnline,
		im.messageFromID,
		ud2.nameOnline AS spamFromNameOnline,
		im.message,
		im.date,
		COALESCE(im.IP, '') AS FROM_IP,
		im.checked,
		ud2.date AS memberSince,
		COUNT(messageFromID) OVER (PARTITION BY im.messageFromID) AS OutBoundMessages,
		COUNT(messageFromID) OVER (PARTITION BY im.messageFromID, im.message) AS OutBoundMessages_Duplicates,
		COUNT(spamReportID) OVER (PARTITION BY SR.offendingID) AS totalSpamComplaints,
		DATEDIFF(DAY, ud2.date, GETDATE()) AS membershipDays,
		c.OutBoundComments,
		b.OutBoundMail,
		a.OutBoundPhotoComments 
FROM		tblSpamReports AS sr
INNER JOIN	tblUserDetails AS ud ON ud.userID = sr.reportedByUserID
INNER JOIN	tblSpamReports_Types AS srt ON srt.spamTypeID = sr.spamTypeID
INNER JOIN	tblInstantMessage AS im ON im.instantMessageID = sr.offendingID
INNER JOIN	tblUserDetails AS ud2 ON ud2.userID = im.messageFromID
LEFT JOIN	(
			SELECT		commentFromID,
					COUNT(commentFromID) AS OutBoundComments
			FROM		tblComment
			GROUP BY	commentFromID
		) AS a ON a.commentFromID = im.messageFromID
LEFT JOIN	(
			SELECT		messageFrom
					COUNT(messageFrom) AS AS OutBoundMail
			FROM		tblMessage
			GROUP BY	messageFrom
		) AS b ON b.messageFrom = im.messageFromID
LEFT JOIN	(
			SELECT		commentFromID,
					COUNT(commentFromID) AS AS OutBoundComments
			FROM		tblComment
			GROUP BY	commentFromID
		) AS c ON c.commentFromID = im.messageFromID
ORDER BY	totalSpamComplaints desc

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources