Want to fetch all row and counts in Postgresql

Question

I have a table like this

|   ids   | name     |  status    |
----------------------------------
|    1    | name1    |   true     |
|    2    | name2    |   true     |
|    3    | name1    |   true     |
|    4    | name4    |   true     |
|    5    | name1    |   false    |
|    6    | name2    |   false    |
|    8    | name1    |   false    |
|    9    | name1    |   true     |
|    10   | name1    |   false    |
|    11   | name1    |   false    |

I want to fetch the row where (name = name1 or name2) and count of all true status(for eg. in the table total_true_count=4 [ie. all row of these ids ids=1,2,3,9] ) and all false status(for eg. in the table total_false_count=5 ie. [all row of these ids=5,6,8,10,11] ) of the selected row.

Output will be like this

$output=Array
                (
                    [total_true_count] => 4,
                    [total_false_sms] => 5,
                    [row_data]=>{row1,row2....}
                )

I tried count function but it not working for me. can anyone please help

It is a little hard to understand your question. I think what might clear it up is if you show a sample of the output you want. — wallyk
– wallyk, Commented May 7, 2020 at 17:17

Marth · Accepted Answer · 2020-07-21 10:00:25Z

1

You can use array_agg to group up values in an array, and use FILTER to limit values fed to COUNT(*):

WITH data(id, name, status) AS (
    VALUES (1, 'name1', TRUE)
         , (2, 'name2', TRUE)
         , (3, 'name1', TRUE)
         , (4, 'name4', TRUE)
         , (5, 'name1', FALSE)
         , (6, 'name2', FALSE)
         , (8, 'name1', FALSE)
         , (9, 'name1', TRUE)
         , (10, 'name1', FALSE)
         , (11, 'name1', FALSE)
)
SELECT array_agg(id) AS row_data -- or array_agg(data.*) if you want all columns
     , COUNT(*) FILTER (WHERE status)     AS total_count_true
     , COUNT(*) FILTER (WHERE NOT status) AS total_count_false
FROM data
WHERE name IN ('name1', 'name2')

returns

+---------------------+----------------+-----------------+
|row_data             |total_count_true|total_count_false|
+---------------------+----------------+-----------------+
|{1,2,3,5,6,8,9,10,11}|4               |5                |
+---------------------+----------------+-----------------+

or, using array_agg(data.*) :

+----------------------------------------------+----------------+-----------------+
|row_data                                      |total_count_true|total_count_false|
+----------------------------------------------+----------------+-----------------+
|{"(1,name1,t)","(2,name2,t)",…,"(11,name1,f)"}|4               |5                |
+----------------------------------------------+----------------+-----------------+

edited Jul 21, 2020 at 10:00

answered May 7, 2020 at 17:32

Marth

24.9k3 gold badges68 silver badges76 bronze badges

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

5 Comments

bala Over a year ago

can you please see the output formate i have just added @Marth i want complete row not only id's

bala Over a year ago

yes i saw that can you please make one more edit and let me know that i want these rows complete data {1,2,3,5,6,8,9,10,11} rather than this id's @Marth

bala Over a year ago

can i use table name in the place of the values as you put the whole table in value i mean VALUES (1, 'name1', TRUE) , (2, 'name2', TRUE) , (3, 'name1', TRUE) , (4, 'name4', TRUE) , (5, 'name1', FALSE) , (6, 'name2', FALSE) , (8, 'name1', FALSE) , (9, 'name1', TRUE) , (10, 'name1', FALSE) , (11, 'name1', FALSE) @Marth

Marth Over a year ago

@bala: yes you can ignore everything up to the SELECT and use your table instead of 'FROM data', this is simply a way for me to have some data to show in the output.

bala Over a year ago

Hi @Marth i have posted one more question please can you help me. This is working fine.

Collectives™ on Stack Overflow

Want to fetch all row and counts in Postgresql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related