Prev: Parse Name Field into First Name Last Name
Next: statistic data about procedures
From: Hugo Kornelis on 9 Dec 2009 17:44
On Wed, 9 Dec 2009 13:07:46 -0800, tshad wrote:

>I could do something like:
>
>SELECT name, sent_count, sent_value, opened_count, opened_value,
>clicks_count, clicks_value,
> sent_count + opened_count + clicks_count as total_counts
>FROM (
>SELECT name,
> SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS
>sent_count,
> SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS
>sent_value,
> SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) AS
>opened_count,
> SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS
>opened_value,
> SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END)
>AS clicks_count,
> SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS
>clicks_value
>FROM Transactions AS t
>JOIN Personnel AS p
> ON t.PersonnelID = p.PersonnelID
>JOIN TranType AS tt
> ON t.tranTypeID = tt.TranTypeID
>WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
>GROUP BY name) as A
>
>and it works but was wonding if there is a better (different) way to do this
>without the SubQuery?

Hi Tom,

SELECT name,
SUM(CASE WHEN TranDescription = 'Sent'
THEN [count] ELSE 0 END) AS sent_count,
SUM(CASE WHEN TranDescription = 'Sent'
THEN value ELSE 0 END) AS sent_value,
SUM(CASE WHEN TranDescription = 'Opened'
THEN [count] ELSE 0 END) AS opened_count,
SUM(CASE WHEN TranDescription = 'Opened'
THEN value ELSE 0 END) AS opened_value,
SUM(CASE WHEN TranDescription = 'Clicked'
THEN [count] ELSE 0 END) AS clicks_count,
SUM(CASE WHEN TranDescription = 'Clicked'
THEN value ELSE 0 END) AS clicks_value,
SUM([count]) AS total_counts
FROM Transactions AS t
INNER JOIN Personnel AS p
ON t.PersonnelID = p.PersonnelID
INNER JOIN TranType AS tt
ON t.tranTypeID = tt.TranTypeID
WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
GROUP BY name;

And I advice you to add the correct prefix to the name, TranDescription,
count, and value columns. It improves human understanding and proofs
against possible problems is the table design ever changes.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: Plamen Ratchev on 9 Dec 2009 17:46
Using a derived table like you did is a good way to handle it to avoid repeating the expressions.

Here is how you can do the same repeating the expressions:

SELECT name,
SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS sent_count,
SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS sent_value,
SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) AS opened_count,
SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS opened_value,
SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) AS clicks_count,
SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS clicks_value,
SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) +
SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) +
SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) AS total_count
FROM Transactions AS t
JOIN Personnel AS p
ON t.PersonnelID = p.PersonnelID
JOIN TranType AS tt
ON t.tranTypeID = tt.TranTypeID
WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
GROUP BY name;

Since you already filter on the transaction descriptions, then it can be simplified to this:

SELECT name,
SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS sent_count,
SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS sent_value,
SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) AS opened_count,
SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS opened_value,
SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) AS clicks_count,
SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS clicks_value,
SUM(CAST([count] AS INT)) AS total_count
FROM Transactions AS t
JOIN Personnel AS p
ON t.PersonnelID = p.PersonnelID
JOIN TranType AS tt
ON t.tranTypeID = tt.TranTypeID
WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
GROUP BY name;

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 9 Dec 2009 18:41
So all three do the job.

I assume yours is a little more efficient as you don't have to create a
derived table.

Thanks,

Tom


"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:a9ednS4ZrfEjtb3WnZ2dnUVZ_jpi4p2d(a)speakeasy.net...
> Using a derived table like you did is a good way to handle it to avoid
> repeating the expressions.
>
> Here is how you can do the same repeating the expressions:
>
> SELECT name,
> SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS
> sent_count,
> SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS
> sent_value,
> SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END)
> AS opened_count,
> SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS
> opened_value,
> SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END)
> AS clicks_count,
> SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS
> clicks_value,
> SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) +
> SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) +
> SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END)
> AS total_count
> FROM Transactions AS t
> JOIN Personnel AS p
> ON t.PersonnelID = p.PersonnelID
> JOIN TranType AS tt
> ON t.tranTypeID = tt.TranTypeID
> WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
> GROUP BY name;
>
> Since you already filter on the transaction descriptions, then it can be
> simplified to this:
>
> SELECT name,
> SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS
> sent_count,
> SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS
> sent_value,
> SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END)
> AS opened_count,
> SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS
> opened_value,
> SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END)
> AS clicks_count,
> SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS
> clicks_value,
> SUM(CAST([count] AS INT)) AS total_count
> FROM Transactions AS t
> JOIN Personnel AS p
> ON t.PersonnelID = p.PersonnelID
> JOIN TranType AS tt
> ON t.tranTypeID = tt.TranTypeID
> WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
> GROUP BY name;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 9 Dec 2009 19:00
Derived tables and CTEs in general have no effect on performance, they are not materialized and the optimizer generates
a single execution plan the query.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Hugo Kornelis on 11 Dec 2009 19:13
On Fri, 11 Dec 2009 15:50:29 -0800, tshad wrote:

>How would I do a similar thing where I don't know what the TranDescriptions
>were but I want them to be the columns (except for the name).
>
>If I do:
>
>SELECT name,Count,TranDescription
>FROM Transactions AS t
>INNER JOIN Personnel AS p
> ON t.PersonnelID = p.PersonnelID
>INNER JOIN TranType AS tt
> ON t.tranTypeID = tt.TranTypeID
>
>I get the following:
>
>Name Count TranDescription
>Joe Smith 10 Sent
>Joe Smith 15 Opened
>Joe Smith 22 Clicked
>Joe Smith 50 Views
>Larry Jones 23 Sent
>Larry Jones 200 Opened
>Larry Jones 300 Clicked
>Larry Jones 250 Views
>Frank Garret 33 Sent
>Frank Garret 200 Views
>
>But I want the each TranDescriptions to be a column, like:
>
>Name Sent Opened Clicked Views
>Joe Smith 10 15 22 50
>Larry Jones 23 200 300 250
>Frank Garret 33 200
>
>If nobody has Opened, I don't want there to be a column for Opened.
>
>I assume I could use a Pivot table here but I have to run this on an Sql
>2000 server.

Hi Tom,

Google "dynamic pivot sql server".

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Parse Name Field into First Name Last Name
Next: statistic data about procedures