Solved

Count statistics in sql

Posted on 2014-07-17
8
154 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
  • 4
  • 3
8 Comments
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:peternordberg
Comment Utility
Here you go!
groupmail.csv
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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:ScottPletcher
Comment Utility
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 31

Accepted Solution

by:
awking00 earned 500 total points
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now