Formatting output of query, need help

Question

Post reply

Formatting output of query, need help

wolfeyes333

SSC Rookie

Points: 29
More actions
December 21, 2014 at 6:37 pm

#313195

I have a sql query that returns the follow columns, email ID, email, status ( 1 - Open, 2-Click, 3- Unsub, 5 - Conversion)
6207 abbdie@nofavtt.org 2
6207 abbdie@nofavtt.org 5
6207 abbdie@nofavtt.org 2
5011 abhayy.bisarya@ncickc.com 2
5011 abhayy.bisarya@ncickc.com 2
5011 abhayy.bisarya@ncickc.com 5
5011 abhayy.bisarya@ncickc.com 5
1236 abigaill@abigailhueller.com 2
1236 abigaill@abigailhueller.com 5
2283 accent@mcleodusa.net 3
5522 achung@kebus.com 2
5522 achung@kebus.com 2
This SQL query retrieves the email history so what I would like to do is to have the following columns:
Email ID, Email, % Open, % Click, % Unsub, % Conversion.
So for example the first email:
6207 abbdie@nofavtt.org 2
6207 abbdie@nofavtt.org 5
6207 abbdie@nofavtt.org 2
has 3 history records, 2 times that person clicked and 1 time converted. So the formatting I'm looking for is:
Email ID, Email, % Open, % Click, % Unsub, % Conversion.
6207,abbdie@nofavtt.org,0%, 66%,0%,33%
I know I can do this with writing code with say coldfusion to talk to the database and format the output but I want to know if I can do this strictly in SQL
Thanks for your time in advance,
Rishi

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86908 More actions · Answer 1

Help us help you next time by providing consumable sample data as I have done.

WITH SampleData (EmailID, Email, [Status]) AS(

SELECT 6207,'abbdie@nofavtt.org',2

UNION ALL SELECT 6207,'abbdie@nofavtt.org',5

UNION ALL SELECT 6207,'abbdie@nofavtt.org',2

UNION ALL SELECT 5011,'abhayy.bisarya@ncickc.com',2

UNION ALL SELECT 5011,'abhayy.bisarya@ncickc.com',5

UNION ALL SELECT 1236,'abigaill@abigailhueller.com',2

UNION ALL SELECT 1236,'abigaill@abigailhueller.com',5

UNION ALL SELECT 2283,'accent@mcleodusa.net',3

UNION ALL SELECT 5522,'achung@kebus.com',2

)

SELECT EmailID, Email

,[% Open] = 100.*COUNT(CASE WHEN [Status]=1 THEN 1 END)/COUNT(*)

,[% Click] = 100.*COUNT(CASE WHEN [Status]=2 THEN 1 END)/COUNT(*)

,[% Unsub] = 100.*COUNT(CASE WHEN [Status]=3 THEN 1 END)/COUNT(*)

,[% Conversion] = 100.*COUNT(CASE WHEN [Status]=5 THEN 1 END)/COUNT(*)

FROM SampleData a

GROUP BY EmailID, Email;

Merry Christmas!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

wolfeyes333 SSC Rookie Points: 29 More actions · Answer 2

Thanks alot for the input it works but I just noticed that I want to only get the percentages for the Clicks and Conversions and ignore the unsubs and Opens, so for

,[% Click] = 100.*COUNT(CASE WHEN [f_action_id]=2 THEN 1 END)/COUNT(*)

,[% Conversion] = 100.*COUNT(CASE WHEN [f_action_id]=5 THEN 1 END)/COUNT(*)

I want to do something like:

,[% Click] = 100.*COUNT(CASE WHEN [f_action_id]=2 THEN 1 END)/COUNT( where f_action_id = 2 and 5)

,[% Conversion] = 100.*COUNT(CASE WHEN [f_action_id]=5 THEN 1 END)/COUNT(where f_action_id = 2 and 5)

so the number being divided only includes the count for clicks and conversions

Can you help me with this?

ScottPletcher SSC Guru Points: 100948 More actions · Answer 3

I typically see %s with one decimal place. If you want more, adjust the "decimal(4, 1)" to (5, 2) or whatever you prefer.

SELECT EmailID, Email

,[% Open] = CAST(100.0 * Open_Count / Total_Count AS decimal(4, 1))

,[% Click] = CAST(100.0 * Click_Count / Click_Plus_Conversion_Count AS decimal(4, 1))

,[% Unsub] = CAST(100.0 * Unsub_Count / Total_Count AS decimal(4, 1))

,[% Conversion] = CAST(100.0 * Conversion_Count / Click_Plus_Conversion_Count AS decimal(4, 1))

FROM (

SELECT

EmailID, Email,

COUNT(CASE WHEN [Status]=1 THEN 1 END) AS Open_Count,

COUNT(CASE WHEN [Status]=2 THEN 1 END) AS Click_Count,

COUNT(CASE WHEN [Status]=3 THEN 1 END) AS Unsub_Count,

COUNT(CASE WHEN [Status]=5 THEN 1 END) AS Conversion_Count,

COUNT(*) AS Total_Count

FROM SampleData

GROUP BY EmailID, Email

) AS counts

CROSS APPLY (

--prevent divide by zero error(s)

SELECT CASE WHEN (Click_Count + Conversion_Count) = 0 THEN 1 ELSE (Click_Count + Conversion_Count) END AS Click_Plus_Conversion_Count

) AS assign_alias_name

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

wolfeyes333 SSC Rookie Points: 29 More actions · Answer 4

Thanks for the update, I'll check on this

Again thanks for helping me out, I really appreciate it

Happy Holidays

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 5

CELKO (12/25/2014)
Please follow basic Netiquette and post the DDL we need to answer this.

I'm thinking you're a bit late with that on this one, Joe. 😉

Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements -- you have no idea.

This is probably not the fault of the original poster.

You should follow ISO-8601 rules for displaying temporal data.

Temporal data isn't required for this problem.

We need to know the data types, keys and constraints on the table.

Again, a bit late here. It's also not a "table". As the OP clearly stated, it's the result of a query.

Avoid dialect in favor of ANSI/ISO Standard SQL.

1. I don't often use the word but that's "Rubbish". A lot of the power of any given SQL Engine is in the extensions of the language. And true portability is a myth.

2. Where do you see any "dialect" being used in the original post?

And you probably need to read and download the PDF for:
https://www.simple-talk.com/books/sql-books/119-sql-code-smells/

Except for the "Status" column name, I'm curious what you see in the original post that would require such a reading especially considering that the OP probably didn't design the underlying tables.

What you posted is not a table! Duplicate rows, so you cannot have a key. A magic generic status, a email_something, etc. If this is a history, where is the temporal dimension? Please read this:
https://www.simple-talk.com/sql/t-sql-programming/state-transition-constraints/

Obviously you didn't read the original post where the OP clearly states "I have a sql query that returns the follow columns..." nor do you understand that the OP is trying to count the duplicates as a percentage of the whole. The OP has correctly returned just enough data to do what is being asked, although a bit of pre-aggregation would be a good first step to realize.

Merry Christmas, Joe! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)