Group By what ???
-
Hi All....
SELECT dbo.t_MailOut.MsgID, dbo.t_MailOut.ToDisplayName, dbo.t_MailOut.ToEmailAddress, dbo.t_MailOut.Subject, dbo.t_MailOut.Folder,
dbo.t_MailOut.Status, dbo.t_MailOut.CreationDateTime, COUNT(dbo.t_Attachments.AttachmentID)
FROM dbo.t_MailOut LEFT OUTER JOIN
dbo.t_Attachments ON dbo.t_MailOut.MsgID = dbo.t_Attachments.MsgID
WHERE (dbo.t_MailOut.UserID = @user-id)
GROUP BY dbo.t_MailOut.MsgID, dbo.t_MailOut.ToDisplayName, dbo.t_MailOut.ToEmailAddress, dbo.t_MailOut.Subject, dbo.t_MailOut.Folder,
dbo.t_MailOut.Status, dbo.t_MailOut.CreationDateTime, (dbo.t_Attachments.AttachmentID)
Returns :
16 Dummy Spit dummy@dummy.com This is the subject text of the mail message NULL U 2005-08-12 11:55:43.123 0
17 Dummy Spit dummy@dummy.com This is the subject text of the mail message NULL D 2005-08-12 11:56:26.763 0
18 Dummy Spit dummy@dummy.com This is the subject text of the mail message NULL D 2005-08-12 11:57:31.373 0
19 Dummy Spit dummy@dummy.com This is the subject text of the mail message NULL U 2005-08-12 11:59:49.310 1
19 Dummy Spit dummy@dummy.com This is the subject text of the mail message NULL U 2005-08-12 11:59:49.310 1
21 Dummy Spit dummy@dummy.com This is the subject text of the mail message NULL D 2005-08-12 12:22:38.760 0
Im trying to group it so that the end result is as follows..return all rows but to group the duplicates and show the count for them. As you can see only one rwo for 19 shown but the number 2 at the end to indicate 2 rows.
16 Dummy Spit dummy@dummy.com This is the subject text of the mail message NULL U 2005-08-12 11:55:43.123 0
17 Dummy Spit dummy@dummy.com This is the subject text of the mail message NULL D 2005-08-12 11:56:26.763 0
18 Dummy Spit dummy@dummy.com This is the subject text of the mail message NULL D 2005-08-12 11:57:31.373 0
19 Dummy Spit dummy@dummy.com This is the subject text of the mail message NULL U 2005-08-12 11:59:49.310 2
21 Dummy Spit dummy@dummy.com This is the subject text of the mail message NULL D 2005-08-12 12:22:38.760 0
Please can you shed some light for me.
Many thanks
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Remove (dbo.t_Attachments.AttachmentID) from the Group By clause. Since you want a count of it, you don't also want to group by it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Was right before my eyes, thank you very much for you help.
Hope this helps...
Ford Fairlane
Rock and Roll Detective
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply