How to create subquery in postgresql

Question

I am very new to postgresql. I have this table.

From this table I want the data like

region                country          count
---------------------------------------------------
UttarPradesh           India              16
California            United State        2

How to achieve this task. I did something but not properly end with the solution here is my queries

CREATE TEMP TABLE temp1 AS
    (SELECT distinct region_name, country_name,  null::integer as "count"
     from opens where track_id=42);

After this I read this result then in a loop run update query with select statement.

So it is a right way ? Or any another way to achieve this task ?

meta.stackoverflow.com/questions/285551/…

user330315
– user330315

2016-12-15 09:33:51 +00:00
Commented Dec 15, 2016 at 9:33 — user330315
– user330315, Commented Dec 15, 2016 at 9:33

e4c5 · Accepted Answer · 2016-12-15 09:32:23Z

5

Why a subquery or temp tables, why not just

SELECT region_name, country_name, COUNT(*) 
FROM opens WHERE track_id = 42 GROUP by region_name, country_name

It's extremely rare in postgresql to have to use a temporary table. If you want to update this or another table with this data, you can easily do so with a well thought out query. Looping is equally undesirable.

answered Dec 15, 2016 at 9:32

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

Comments

fafl · Accepted Answer · 2016-12-15 09:32:45Z

1

SELECT region_name, country_name, COUNT(*)
FROM t GROUP BY region_name, country_name

answered Dec 15, 2016 at 9:32

fafl

7,5074 gold badges32 silver badges54 bronze badges

Collectives™ on Stack Overflow

How to create subquery in postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related