Select Based on Column Value in Postgres

Question

I want to get values of TIMESTAMP and STRING_VALUE based on selected ID. Suppose My Selected ID is 4259,4226 and 4259 Then It should select TIMESTAMP and STRING_VALUE for selected ID using CASE Statement. I have tried Below query but returning Into Error

CREATE TABLE "DRL_FTO3_DI1_A0"
(
  "VARIABLE" integer,
  "CALCULATION" integer,
  "TIMESTAMP_S" integer,
  "TIMESTAMP_MS" integer,
  "VALUE" double precision,
  "STATUS" integer,
  "GUID" character(36),
  "STRVALUE" character varying(50)
)

INSERT INTO "DRL_FTO3_DI1_A0"(
            "VARIABLE", "CALCULATION", "TIMESTAMP_S", "TIMESTAMP_MS", "VALUE", 
            "STATUS", "GUID", "STRVALUE")
    VALUES (4226, 0, 1451120925, 329,0 , 1078067200, '', 'BATCH 1'),
           (4306, 0, 1451120925, 329,0 , 1078067200, '', 'BATCH 2'),
           (4311, 0, 1451120925, 329,0 , 1078067200, '', '2')

Now Suppose Out of three Variable(4226,4306,4311) I want to select 4226 and 4311

SELECT ((TIMESTAMP WITHOUT Time Zone 'epoch' + "TIMESTAMP_S" * INTERVAL '1 second') AT TIME ZONE 'UTC')::TIMESTAMP WITHOUT Time Zone,
SUM(CASE WHEN "VARIABLE" = 4226 Then "STRVALUE" END) as 'A',
SUM(CASE WHEN "VARIABLE" = 4311 Then "STRVALUE" END) as 'B'
FROM "DRL_FTO3_DI1_A0"
GROUP BY "TIMESTAMP_S"
ORDER BY "TIMESTAMP_S";


 TIMESTAMP_S                         A            B
 2015-12-26 14:38:45                BATCH_1       2

I don't understand what you're trying to do. Perhaps example data (and error message) would help? — thebjorn
– thebjorn, Commented Dec 26, 2015 at 10:49
Will the crosstab() function do what you want? stackoverflow.com/questions/3002499/postgresql-crosstab-query/… — thebjorn
– thebjorn, Commented Dec 26, 2015 at 11:27
Can you please tell me how can i write the query for crosstab in my case — Parth Desai
– Parth Desai, Commented Dec 26, 2015 at 11:30
This is what i have tried using crosstab but resulting into error SELECT * FROM crosstab( 'SELECT "VARIABLE","TIMESTAMP_S","STRVALUE" FROM "DRL_FTO3_DI1_A0" ORDER BY 2') AS ct ("TIMESTAMP_S" Integer, "A" text, "B" text); — Parth Desai
– Parth Desai, Commented Dec 26, 2015 at 11:46

Parth Desai · Accepted Answer · 2015-12-26 12:14:59Z

1

This Is the Query using crosstab and It Works

SELECT *
    FROM  crosstab (
        $$SELECT "VARIABLE", "TIMESTAMP_S", "STRVALUE"
         FROM   "DRL_FTO3_DI1_A0"
         WHERE  "VARIABLE" = ANY (array[4306,4226])
         ORDER  BY 1,2$$
    ) 
        AS
    t (            
        "TIMESTAMP_S" integer,                
        "A" character varying,
        "B" character varying
    );

answered Dec 26, 2015 at 12:14

Parth Desai

2092 gold badges4 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Select Based on Column Value in Postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related