Count statistics in sql

Hi,

I have this table looking like this:
GroupMail
I would like to count the how many times each event occurs based on each newsletter. So that I get a result like this:
Processed Delivered   Open  Click  Deferred newsletterID
25                22              15       3         3               9

How can I achieve that with an sql query?

Peter
Peter NordbergIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Commented:
I can only assume that the data shown above is not the complete set to produce the results you are looking for since there are are fewer processed and delivered events, only 2 open and click events shown, no deferred events and the number of newsletter ids shown are six and not nine. If so, can you produce the entire set of data in a format that can be used to re-create the table so we can test(i.e. not a picture) such as a text or excel file. Even better would be to show the table create with the insert statements that populated that data.
0
Peter NordbergIT ManagerAuthor Commented:
Yes, the image shows only a fraction of the data. Here is the insert query for the table:

INSERT INTO [dbo].[customerGroupMailStatistics]
           ([customerID]
           ,[newsLetterID]
           ,[event]
           ,[url]
           ,[response]
           ,[timestamp]
           ,[tID]
           ,[email])
     VALUES
           (<customerID, int,>
           ,<newsLetterID, int,>
           ,<event, nvarchar(50),>
           ,<url, nvarchar(300),>
           ,<response, nvarchar(500),>
           ,<timestamp, nvarchar(50),>
           ,<tID, int,>
           ,<email, nvarchar(200),>)
GO

Open in new window


Thanks for help!

Peter
0
Peter NordbergIT ManagerAuthor Commented:
Here is a create by the way so it gets faster with testing:
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[customerGroupMailStatistics](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[customerID] [int] NULL,
	[newsLetterID] [int] NULL,
	[event] [nvarchar](50) NULL,
	[url] [nvarchar](300) NULL,
	[response] [nvarchar](500) NULL,
	[timestamp] [nvarchar](50) NULL,
	[tID] [int] NULL,
	[email] [nvarchar](200) NULL,
 CONSTRAINT [PK_customerGroupMailStatistics] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Open in new window


Peter
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

awking00Commented:
What really is needed is to see all of the actual data. Can you just post the results of this query (in a text file will do)?
select id, customerid, newsletterid, event from [dbo.customerGroupMailStatistics] order by 1;
0
Peter NordbergIT ManagerAuthor Commented:
Here you go!
groupmail.csv
0
awking00Commented:
With some 35,000 records, I am assuming there will be many more than 25 processed events (the same being true for the other event values). Are the results you initially showed as wanting to get just an example or based on some filter applied to limit the number of rows?
0
Scott PletcherSenior DBACommented:
SELECT    
           SUM(CASE WHEN event = 'Processed' THEN 1 ELSE END) AS Processed
           ,SUM(CASE WHEN event = 'Delivered' THEN 1 ELSE END) AS Delivered
           ,SUM(CASE WHEN event = 'Open' THEN 1 ELSE END) AS Open
           ,SUM(CASE WHEN event = 'Click' THEN 1 ELSE END) AS Click
           ,SUM(CASE WHEN event = 'Deferred' THEN 1 ELSE END) AS Deferred
           ,[newsLetterID]
GROUP BY
           [newsLetterID]
0
awking00Commented:
Got tied up with something else for a while.
select
sum(case when event = 'processed' then 1 else 0 end) processed,
sum(case when event = 'delivered' then 1 else 0 end) delivered,
sum(case when event = 'open' then 1 else 0 end) open,
sum(case when event = 'click' then 1 else 0 end) click,
sum(case when event = 'deferred' then 1 else 0 end) deferred,
count(distinct newsletterid) newsletterid
from groupmail
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.