CODEWITHSUNDEEP
postgresql
12B01
12B01

Reputation: 181

How to use count as selection criteria in PostgreSQL

I have an existing table1 contains "account" and "tax_year". I want to create a new table2 with the two variables from table1 and add two new variables (unq and frequency).

For example, imagine the table below is an intermediate result of the query. The account and year are from the existing table 1. The unq and frequency are generated by the query with the formulas: unq= account & tax_year, frequency=count(unq)

account year    unq frequency
aaa 2014    aaa2014 1
bbb 2014    bbb2014 2
bbb 2014    bbb2014 2
ccc 2015    ccc2015 2
ccc 2015    ccc2015 2
ddd 2014    ddd2014 1
ddd 2015    ddd2015 1
ddd 2016    ddd2016 1

At the end, the query should return the following result:

account year    unq frequency
aaa 2014    aaa2014 1
ddd 2014    ddd2014 1
ddd 2015    ddd2015 1
ddd 2016    ddd2016 1

Here is my code, it doesn't work. I'm fairly new to PostgreSQL.

CREATE TABLE table2 AS
SELECT account::text,
       tax_year::text,
    concat(account,tax_year) AS unq,
    Count(unq) AS frequency 
FROM table1
GROUP BY unq
HAVING Count(unq)=1;

Upvotes: 1

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

If you table is like the one you show, why are you aggregating? You can just use where:

CREATE TABLE table1 AS
    SELECT account::text, tax_year::text, unq AS unique, frequency  
    FROM table2
    WHERE frequency = 1;

If your table contains account and tax_year and you want to do the calculations:

CREATE TABLE table1 AS
    SELECT account::text, tax_year::text, 
           CONCAT(account, tax_year) AS unique,
           COUNT(*) as frequency  
    FROM table2
    GROUP BY account, tax_year
    HAVING COUNT(*) = 1;

Upvotes: 1

Related Questions