RecipeDan
asked on
Sum Function Workaround?
Hello:
I have a Data table that looks like this:
ID PersonName PersonClass
557 Dan P
557 Dan S
557 Dan S
557 Dan P
557 Dan S
557 Dan S
189 Mike S
189 Mike S
189 Mike S
What I want to do is if there is a P in the PersonClass Column then Y if not N. My output will look like this
ID PERSONNAME PC
557 Dan Y
189 Mike N
Here is the script that I am using. It works, however, one of the tables has over 600,000 entries and the sum function really slows it down. Is there a way to a achieve my result without using sum or count?
I have a Data table that looks like this:
ID PersonName PersonClass
557 Dan P
557 Dan S
557 Dan S
557 Dan P
557 Dan S
557 Dan S
189 Mike S
189 Mike S
189 Mike S
What I want to do is if there is a P in the PersonClass Column then Y if not N. My output will look like this
ID PERSONNAME PC
557 Dan Y
189 Mike N
Here is the script that I am using. It works, however, one of the tables has over 600,000 entries and the sum function really slows it down. Is there a way to a achieve my result without using sum or count?
SELECT ID, PERSONNAME, CASE WHEN PC >=1 THEN 'Y' ELSE 'N' END AS PC
FROM
(
SELECT ID, PERSONNAME, SUM(PC) AS PC
FROM
(
select ID, PERSONNAME, case when PERSONCLASS = 'p' THEN 1 ELSE 0 END AS PC FROM Test
)
table1
GROUP BY ID, PERSONNAME
)
TABLE2
select PersonClass, count(PersonClass)
from table
group by PersonClass
will give you how many P's and how many S's overall.Then expand that to individuals
select PersonName, PersonClass, count(PersonClass)
from table
group by PersonName, PersonClass
to get the name, class, and count. Even with a million records it shouldn't take _that_ long, but I doubt that there's a better way.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry I missed the ID in the group by:
select ID, PERSONNAME, case when PERSONCLASS = 'p' THEN 1 ELSE 0 END AS PC FROM Test
GROUP BY ID, PERSONNAME
select ID, PERSONNAME, case when PERSONCLASS = 'p' THEN 1 ELSE 0 END AS PC FROM Test
GROUP BY ID, PERSONNAME
As P < S I would just go for a simple MIN(PERSONCLASS) and then do a case expression on that - you do fewer case expressions this way.
| ID | PERSONNAME | PC |
|-----|------------|----|
| 557 | Dan | Y |
| 189 | Mike | N |
SELECT
id
, PersonName
, CASE WHEN MIN(PERSONCLASS) = 'P' THEN 'Y' ELSE 'N' END as PC
FROM YourTable
GROUP BY id
, PersonName
;
CREATE TABLE YourTable
([ID] int, [PersonName] varchar(4), [PersonClass] varchar(1))
;
INSERT INTO YourTable
([ID], [PersonName], [PersonClass])
VALUES
(557, 'Dan', 'P'),
(557, 'Dan', 'S'),
(557, 'Dan', 'S'),
(557, 'Dan', 'P'),
(557, 'Dan', 'S'),
(557, 'Dan', 'S'),
(189, 'Mike', 'S'),
(189, 'Mike', 'S'),
(189, 'Mike', 'S')
;
http://sqlfiddle.com/#!3/15ef6/4
If you still have performance issues please provide an execution plan (as .sqlplan file)
select ID, PERSONNAME, case when PERSONCLASS = 'p' THEN 1 ELSE 0 END AS PC FROM Test
GROUP BY PERSONNAME