Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Sum Function Workaround?

Posted on 2013-10-24
Medium Priority
256 Views
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
``````
0
Question by:RecipeDan

LVL 40

Expert Comment

ID: 39598821
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

LVL 16

Expert Comment

ID: 39598823
``````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.
0

LVL 21

Accepted Solution

Dale Burrell earned 2000 total points
ID: 39598824
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
``````
0

LVL 40

Expert Comment

ID: 39598830
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

LVL 49

Expert Comment

ID: 39599425
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)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…