Query with postgresql

Question

Here below some sample 3 data, using PostgreSQL and rails

Stock
id: 42324
name: 'n1'

    stock_items

     id: 57889359
     stock_id: 42324
     check_id: 14123
     turn: 5
     mock_id: 57889357

     id: 57889360
     stock_id: 42324
     check_id: 14141
     turn: 3
     mock_id: 0

Stock
id: 42325
name: 'n1'

    stock_items

     id: 57889361
     stock_id: 42325
     check_id: 19499
     turn: 5
     mock_id: 57889359

     id: 57889362
     stock_id: 42325
     check_id: 19500
     turn: 3
     mock_id: 0

Here i have stock table and stock_items table i am trying to take the result like if mock_id is 0 then get the check_id else other check_id whose mock_id is not zero.

So i tried one query

SELECT 
    check_id1, 
    CASE 
        WHEN stock_items.mock_id = 0 THEN stock_items.check_id 
        ELSE stock_items.check_id  
    END as check_id2 
FROM
    stock_items 
    INNER JOIN stocks on stocks.id = stock_items.stock_id ;

but the above query fails, but i need it like below, any suggestions?

check_id1 (mock_id=0) | check_id2 (mock_id !=0)
----------------------+-----------------------
                14141 |    14123
                19500 |    19499

CraftyMousy · Accepted Answer · 2017-07-14 15:16:40Z

2

CASE statement doesn't work like you think. You can read it here https://www.postgresql.org/docs/8.1/static/functions-conditional.html

Your query shoud look like this SELECT s.check_id1, s2.check_id1 FROM stock_items s, (SELECT * FROM stock_items WHERE mock_id <> 0) s2 WHERE s.stock_id = s2.stock_id

answered Jul 14, 2017 at 15:16

CraftyMousy

812 bronze badges

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

Comments

joanolo · Accepted Answer · 2017-07-15 11:52:00Z

1

What you're trying to do is basically a PIVOT. You should actually use two CASE, within an aggregation function, and having a GROUP BY (and an ORDER BY to get the order you gave):

SELECT 
    stock_id,
    max(CASE when mock_id =0 then check_id END) AS "check_id1 (mock_id=0)", 
    max(CASE when mock_id<>0 then check_id END) AS "check_id2 (mock_id!=0)"
FROM
    stock_items 
    INNER JOIN stocks on stocks.id = stock_items.stock_id 
GROUP BY
    stock_id 
ORDER BY
    stock_id;

stock_id | check_id1 (mock_id=0) | check_id2 (mock_id!=0)
-------: | --------------------: | ---------------------:
   42324 |                 14141 |                  14123
   42325 |                 19500 |                  19499

NOTE: I've added the stock_id so that the result is easier to understand. You can omit it if you don't actually want it in your response.

You can check all the setup and test it at dbfiddle here

UPDATE

As per comments

SELECT
    stock_id,
    -- This will get rid of the null in array_agg
    ARRAY(SELECT 
        e
     FROM 
         unnest(all_check_ids_with_mock_id_0) AS e
     WHERE 
         e IS NOT NULL
    ) AS all_check_ids_with_mock_id_0,
    ARRAY(SELECT
        e
    FROM 
        unnest(all_check_ids_with_mock_id_non_0) AS e
    WHERE 
        e IS NOT NULL
    ) AS all_check_ids_with_mock_id_non_0
FROM
(
    SELECT 
        stock_id, 
        array_agg(CASE WHEN mock_id =0 THEN check_id END) AS all_check_ids_with_mock_id_0,
        array_agg(CASE WHEN mock_id<>0 THEN check_id END) AS all_check_ids_with_mock_id_non_0
    FROM
        stock_items 
        INNER JOIN stocks on stocks.id = stock_items.stock_id 
    GROUP BY
        stock_id
    ) AS q0
ORDER BY
    stock_id;

stock_id | all_check_ids_with_mock_id_0 | all_check_ids_with_mock_id_non_0
-------: | :--------------------------- | :-------------------------------
20223028 | {48752}                      | {194907,19260}                  
20223029 | {48743}                      | {194945,194907}                 
20223030 | {48752}                      | {194907}                        
20223031 | {48752}                      | {194907}

dbfiddle here

edited Jul 15, 2017 at 11:52

answered Jul 14, 2017 at 23:14

joanolo

6,3881 gold badge33 silver badges40 bronze badges

8 Comments

Developer Over a year ago

Thanks for the comment, i had tried that with case, here i have updated that fiddle dbfiddle.uk/…, i need one more clarificaiton here i can't user max becausue i need all the data also is there any way to avoid the duplicate of check_id1 and check_id2?

joanolo Over a year ago

In your dbfiddle: If your pivot column is mock_id, you shouldn't GROUP BY it. If you want all your data, you can use array_agg instead of max. you'll get an array of values if there is more than one. If you want to unpack that info again, you can use later unnest. I don't fully get it, because I don't see any array with more than one element: dbfiddle here

Developer Over a year ago

thanks, I mean in this dbfiddle.uk/… that order ids (20223030, 20223031) where the check_id1 and check_id2 is repeating so is there any way to avoid that.

joanolo Over a year ago

I still don't get it. My answer gave the result you asked for in your question. I think you should rephrase your question and put an example of what you really want. I think you just don't want to group by stock_items.check_id... My latest *speculation dbfiddle here

Developer Over a year ago

Yes, now i can tell you, in this dbfiddle.uk/… , showing the fields all_check_ids CASE 1: for example {194945,48743,194907}, here the 48743 is mock_id 0 value, and other two value are mock_id non zero, i need it like one column have 48743 and other column have 194945, 194907. CASE 2: for example {194907,48752} here this combination is repeating in that fiddle i want to avoid that repetition also. it will be helpful if you can open a chat.

|

Collectives™ on Stack Overflow

Query with postgresql

2 Answers 2

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related