Solved

Count statistics in sql

Posted on 2014-07-17
8
157 Views
Last Modified: 2014-07-18
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
0
Comment
Question by:peternordberg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 40202794
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
 

Author Comment

by:peternordberg
ID: 40202822
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
 

Author Comment

by:peternordberg
ID: 40202829
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 32

Expert Comment

by:awking00
ID: 40202845
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
 

Author Comment

by:peternordberg
ID: 40202858
Here you go!
groupmail.csv
0
 
LVL 32

Expert Comment

by:awking00
ID: 40202876
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40203084
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
 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 40203093
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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question