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?

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

Open in new window

LVL 1
RecipeDanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dale BurrellConnect With a Mentor DirectorCommented:
Its unlikely to be the 'sum' which is an issue... 600000 records isn't many in the great database scheme of things.

It could be the fact that you have 3 nested queries which might be preventing the optimiser from working. Or it could be the lack of a good index.

I recon this, simplified, query will produce the same result - don't know if it is faster though.

select id, PersonName, max(case when PERSONCLASS = 'p' THEN 'Y' ELSE 'n') END
from Test
group by id, PersonName

Open in new window

0
 
lcohanDatabase AnalystCommented:
Did you tried simple CASE statement like:



select ID, PERSONNAME, case when PERSONCLASS = 'p' THEN 1 ELSE 0 END AS PC FROM Test
GROUP BY PERSONNAME
0
 
DcpKingCommented:
select PersonClass, count(PersonClass) 
from table 
group by PersonClass

Open in new window

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

Open in new window

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.
0
 
lcohanDatabase AnalystCommented:
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
0
 
PaulCommented:
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 

Open in new window

If you still have performance issues please provide an execution plan (as .sqlplan file)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.