0

In my database I have the following table:

Mytable
id SERIAL,
category VARCHAR,
value VARCHAR

And I execute the following query:

select category, COUNT(*) from mytable group by category

What I want is to generate a single row value containing the following:

category1 | category2 |  category3
1234      | 3456      |  12345

The table return the following results:

 category   | Value
`category1` | 1234
`category2` | 3456
`category3` | 12345

Do you have any idea how to do this? I have looked the crosstab function but required an extra column named row_name that in my cases does not exist. Also using a second query seems like a waste to me.

The

1

1 Answer 1

1

Use conditional aggregation:

select count(*) filter (where category = 'category1') as category1,
       count(*) filter (where category = 'category2') as category2,
       count(*) filter (where category = 'category3') as category3
from the_table;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.