How to summarize statement in PostgreSQL?

Question

In order to get final results I need to write lots of queries:

SELECT min(x) from table WHERE column1='ab' AND column2='3';
SELECT min(y) from table WHERE column1='ab' AND column2='3';
SELECT max(x) from table WHERE column1='ab' AND column2='3';
SELECT max(y) from table WHERE column1='ab' AND column2='3';
SELECT min(x) from table WHERE column1='ab' AND column2='4';
SELECT min(y) from table WHERE column1='ab' AND column2='4';
SELECT max(x) from table WHERE column1='ab' AND column2='4';
SELECT max(y) from table WHERE column1='ab' AND column2='4';
...

where column2 goes from 3-8.

I guess I can CASE that somehow, and maybe do some sort of FOR loop for column2, but I have no success with it. Or I can do something else with it?

Wanted result:

column2 |  minx  |   miny |   maxx |   maxy |
      3 | number | number | number | number |
      4 | number | number | number | number |
      5 | number | number | number | number |
      6 | number | number | number | number |
      7 | number | number | number | number |
      8 | number | number | number | number |

Any help appriciated!

Evan Steinbrenner · Accepted Answer · 2016-11-03 21:43:15Z

2

Not sure how you'd use a case in that but seems like a simple use for a group by statement.

SELECT Column2, Min(x), Min(y), Max(x), Max(y)
FROM table 
WHERE Column1='ab' AND Column2 > 2 AND Column2 < 9 GROUP BY Column2

I normally do T-SQL for MS-SQL but this is pretty basic so I expect this will work on Postgres

answered Nov 3, 2016 at 21:43

Evan Steinbrenner

2471 silver badge7 bronze badges

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

Comments

Munir · Accepted Answer · 2016-11-03 21:43:42Z

1

Why case? You can just group by column2.

select
    column2,
    min(x) as minx,
    min(y) as miny,
    max(x) as maxx,
    max(y) as maxy
from table
where column1 = 'ab' and column2 between 3 and 8
group by
    column2

answered Nov 3, 2016 at 21:43

Munir

3,6303 gold badges21 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to summarize statement in PostgreSQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related