CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Jonas Palačionis
Jonas Palačionis

Reputation: 4842

Checking if value exists but not grouping it

I am creating a view out of a table like this:

name        lastname        state       number_1        number_2
john        dal             TX          100             200
dave        pop                         100             300
tom         kim             LA          300             600
kyle        rom                         50              10  
kyle        rom             LA          50              10

I am trying to multiply number_1 and number_2 and have a column stating if state was present but not group by it:

SELECT name, lastname, number_1*number_2 as product,
CASE 
WHEN state IS NOT NULL 
THEN True 
ELSE False 
END AS state_present
GROUP BY name, lastname

When I run this I get that state needs to be used in aggregate function, my desired outcome would look like this:

name        lastname       product         state_present       
john        dal            200000          True
dave        pop            300000          False
tom         kim            1800000         True          
kyle        rom            500             False  
kyle        rom            500             True

Upvotes: 0

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

This answers the original version of the question.

Are you looking for a simple select with some expressions?

select name, lastname, (number_1 * number_2) as product,
       (state is not null) as state_present
from t;

Your question seems a bit misleading, because it mentions aggregation but I don't see how that would apply at all.

Upvotes: 1

Related Questions