Peter Nordberg
asked on
Count statistics in sql
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.
ASKER
Yes, the image shows only a fraction of the data. Here is the insert query for the table:
Thanks for help!
Peter
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
Thanks for help!
Peter
ASKER
Here is a create by the way so it gets faster with testing:
Peter
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
Peter
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.customerGroupMailStat istics] order by 1;
select id, customerid, newsletterid, event from [dbo.customerGroupMailStat
ASKER
Here you go!
groupmail.csv
groupmail.csv
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?
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]
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]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.