Adding a count of values

Question

Is there another way I can sum up counts with less code?

I'm using a view (my only option) to try to find out if a customer spent money during any two of the last 5 calendar years

Table name: V_PERSON V_REVENUE

Columns: V_PERSON.ID

V.REVENUE.PersonID
V.REVENUE.Year1 revenue for the year (currently 2016)
V.REVENUE.Year2 revenue for the year (currently, 2015)
V.REVENUE.Year3
V.REVENUE.Year4
V.REVENUE.Year5

Here's what I've tried:

SELECT V_PERSON.ID

FROM V_PERSON

WHERE 
(

(
SELECT '1'
FROM V_REVENUE
WHERE V_REVENUE.PersonID = V_PERSON.ID  
AND V_REVENUE.Year1 > 0
)

+

(
SELECT '1'
FROM V_REVENUE
WHERE V_REVENUE.PersonID = V_PERSON.ID  
AND V_REVENUE.Year2 > 0
)

+

(
SELECT '1'
FROM V_REVENUE
WHERE V_REVENUE.PersonID = V_PERSON.ID  
AND V_REVENUE.Year3 > 0
)

+

(
SELECT '1'
FROM V_REVENUE
WHERE V_REVENUE.PersonID = V_PERSON.ID  
AND V_REVENUE.Year4 > 0
)

+

(
SELECT '1'
FROM V_REVENUE
WHERE V_REVENUE.PersonID = V_PERSON.ID  
AND V_REVENUE.Year5 > 0
)

) >= 2

sgeddes · Accepted Answer · 2016-03-09 01:03:52Z

1

Here's one option using exists with multiple case statements:

select id
from person p 
where exists (
    select 1
    from revenue r 
    where p.id = r.personid
        and case when r.year1 > 0 then 1 else 0 end +
            case when r.year2 > 0 then 1 else 0 end +
            case when r.year3 > 0 then 1 else 0 end +
            case when r.year4 > 0 then 1 else 0 end +
            case when r.year5 > 0 then 1 else 0 end >= 2
)

answered Mar 9, 2016 at 1:03

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harsh · Accepted Answer · 2016-03-09 01:03:37Z

1

How about a CASE statement:

SELECT V_PERSON.ID
FROM V_PERSON
WHERE 
(
SELECT  (case when V_REVENUE.Year1 > 0 then 1 else 0 end) +  
    (case when V_REVENUE.Year2 > 0 then 1 else 0 end) +
    (case when V_REVENUE.Year3 > 0 then 1 else 0 end) +
    (case when V_REVENUE.Year4 > 0 then 1 else 0 end) +
    (case when V_REVENUE.Year5 > 0 then 1 else 0 end) 
FROM V_REVENUE
WHERE V_REVENUE.PersonID = V_PERSON.ID) >= 2

answered Mar 9, 2016 at 1:03

Harsh

1,3178 silver badges14 bronze badges

1 Comment

4ccc4 Over a year ago

I like this one too. Thanks.

Collectives™ on Stack Overflow

Adding a count of values

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related