CODEWITHSUNDEEP
sqlpostgresql
Jason Smith
Jason Smith

Reputation: 127

Same value showing up as three values

I have column (Builder) in my database that I want to update the three values for and make it one single value since it is all the same but our data source treats it separately. I do not know what the dilemma is that even after the update statement, database still treats them as separate values. My data source shows a total of 5 rows and so does my database. What can I do to treat them as one, single value rather than three? 

select * from traffic

Builder                      Contacts

Small Brother                   0
Big Brother                     8 
Brother                         10 
Neighbor                        7 
Friend                          5

I updated values in my table:

update traffic set Builder = 'Brothers' WHERE Builder in ('Brother',
     'Big Brother','Small Brother') ;

When I do 

select distinct Builder from traffic

Builder

Brothers
Neighbor
Friend

It shows up as one value which is 'Brothers' meaning the update statement executed successfully.

Surprisingly, when I run

 select * from traffic

 Builder              Contacts

 Brothers              0
 Brothers              8
 Brothers              10
 Neighbor              7
 Friend                5

When I apply an aggregate function:

select Builder, sum(Contacts)
from traffic
group by Builder, Contacts

Builder                   Sum
Brothers                  8
Brothers                  10
Neighbor                  7 
Friend                    5

The result I need help with:

select * from traffic

Builder              Contacts

Brothers               18  
Neighbor               7
Friend                 5

Upvotes: 0

Views: 40

Answers (2)

Jota Pardo
Jota Pardo

Reputation: 878

if you don´t want to update your data you can try with this:

SELECT 
    CASE WHEN Builder LIKE '%Brother%' THEN 
        'Brother'
    ELSE 
        Builder
    END
    ,SUM(Contacts)
FROM traffic
GROUP BY CASE WHEN Builder LIKE '%Brother%' THEN 
        'Brother'
    ELSE 
        Builder
    END

Upvotes: 1

user7174959
user7174959

Reputation: 

SELECT * FROM traffic

Will give you every column and row in the traffic table. Because you just updated 3 of the rows to say "Brother", you are getting back exactly that. In order to get back the result you want, you need to use a 

GROUP BY

clause along with an aggregate clause.

Something like 

SELECT Builder, SUM(Contacts) 
FROM traffic
GROUP BY Builder

This actually tells the database to first group by the builders, which will get rid of the duplicates, and then selects the count of all contacts. The SELECT statement is ran last in the database.

Upvotes: 5

Related Questions