Postgres sql show a specific string in result based on integer

Question

So I have table with cars and color on said cars. Color is shown as an integer, but i want to map it in the select query so that the respective color is shown instead. A mapping table doesn´t exist and I don´t have write access to the database so I can`t create a mapping table. The query is only meant for a dashboard which only allows SQL queries.

I have a table like this:

| **Color**   | **Car**    |     
| 1       | BMW    |    
| 2       | Nissan   |   
| 1       | Tesla   |

And this query

SELECT "Color", count(*)
FROM "Cars"
GROUP BY "Color"

Which will give this result:

| Color | Count |    
|  1  |   2  |        
|  2  |   1  |

But what I want is this:

| Color | Count |    
| white  |   2  |    
| green  |   1  |

How can i write a query like this?

Join to the colors table that is the reference table with the correct name. — Gordon Linoff
– Gordon Linoff, Commented Jul 2, 2020 at 14:01
That would be the optimal. But said table doesn´t exist and I don´t have write access to the database so I can`t create a mapping table. Updated question with information — Moddaman
– Moddaman, Commented Jul 2, 2020 at 14:03
Gordon was not claiming you have to use a mapping table, but you should use JOIN — Luuk
– Luuk, Commented Jul 2, 2020 at 14:06

Mike Organek · Accepted Answer · 2023-04-20 21:07:10Z

2

If you really do not have a reference table, then you will need to do something like this:

with color_names as (
  select car,
         color,
         case color
           when 1 then 'green'
           when 2 then 'white'
           else 'unknown'
         end as color_name
    from your_table
)
select color_name, count(*) 
  from color_names
 group by color_name;

edited Apr 20, 2023 at 21:07

answered Jul 2, 2020 at 14:14

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

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

1 Comment

Orjanp Over a year ago

Had to make a small change, with color_names as ( ....

Belayer · Accepted Answer · 2020-07-02 20:02:57Z

Ok so "said table doesn´t exist and I don´t have write access". There is a very simple solution here: Go talk to your DBA and ask for it. One of the stupidest ideas in IT today is developers not talking to DBAs (almost as stupid as DBA not responding to developers). Contrary to popular belief you are on the same team.
However, in the event she/he doesn't respond (thus you unable to accomplish @Gordon goal of not repeating logic) but there is a work around - at least until the DBA comes to their sinces. You create a template CTE which can be pulled into queries as needed.

with color_names (id, name) as
     ( values (1,'White')
            , (2,'Green')
     )

Make sure this is stored in a location accessible to the entire team. This can the be pulled in whenever needed.

Then again, on second thought, you're a developer so develop: create a function that returns a table. You can then use this function almost exactly as if it were at table and it has a massive benefit - a single point of maintenance for all queries using it. Not quite the same as just updating a table, but massively better than having to update multiple queries.

Both are incorporated in fiddle.

Collectives™ on Stack Overflow

Postgres sql show a specific string in result based on integer

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related