Revisions to GROUP BY with subqueries for a Group?

replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/

Source Link

edited Apr 13, 2017 at 12:42

1

Use a CASE statement to check if RecordNumber = 0 | 2147483647.

Try this version:

select   Content->>'Group' as "Group", 
         min(case when Content->>'RecordNumber' = '0' 
                  then Content->>'When' 
                  else null 
             end ) "Start",
         max(case when Content->>'RecordNumber' = '2147483647' 
                  then Content->>'When' 
                  else null 
             end ) "Stop",
         count(*) as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

The result:

| Group | Start      | Stop       | Count |
|-------|------------|------------|-------|
| 0     | 1490280300 | 1490280500 | 3     |
| 1     | 1490280600 | 1490280700 | 2     |
| 2     | 1490280900 | NULL       | 2     |

As Evan Carroll Evan Carroll pointed out, you can also take advantage of the FILTER clause. Have a look at 4.2.7 Aggregate expressions in the Postgres docs.

select   Content->>'Group' as "Group", 
         min(Content->>'When') filter (where Content->>'RecordNumber' = '0') "Start",
         max(Content->>'When') filter (where Content->>'RecordNumber' = '2147483647') "Stop",
         count(*)  as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

Check it here: http://rextester.com/ADYG46620

Use a CASE statement to check if RecordNumber = 0 | 2147483647.

Try this version:

select   Content->>'Group' as "Group", 
         min(case when Content->>'RecordNumber' = '0' 
                  then Content->>'When' 
                  else null 
             end ) "Start",
         max(case when Content->>'RecordNumber' = '2147483647' 
                  then Content->>'When' 
                  else null 
             end ) "Stop",
         count(*) as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

The result:

| Group | Start      | Stop       | Count |
|-------|------------|------------|-------|
| 0     | 1490280300 | 1490280500 | 3     |
| 1     | 1490280600 | 1490280700 | 2     |
| 2     | 1490280900 | NULL       | 2     |

As Evan Carroll pointed out, you can also take advantage of the FILTER clause. Have a look at 4.2.7 Aggregate expressions in the Postgres docs.

select   Content->>'Group' as "Group", 
         min(Content->>'When') filter (where Content->>'RecordNumber' = '0') "Start",
         max(Content->>'When') filter (where Content->>'RecordNumber' = '2147483647') "Stop",
         count(*)  as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

Check it here: http://rextester.com/ADYG46620

Use a CASE statement to check if RecordNumber = 0 | 2147483647.

Try this version:

select   Content->>'Group' as "Group", 
         min(case when Content->>'RecordNumber' = '0' 
                  then Content->>'When' 
                  else null 
             end ) "Start",
         max(case when Content->>'RecordNumber' = '2147483647' 
                  then Content->>'When' 
                  else null 
             end ) "Stop",
         count(*) as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

The result:

| Group | Start      | Stop       | Count |
|-------|------------|------------|-------|
| 0     | 1490280300 | 1490280500 | 3     |
| 1     | 1490280600 | 1490280700 | 2     |
| 2     | 1490280900 | NULL       | 2     |

As Evan Carroll pointed out, you can also take advantage of the FILTER clause. Have a look at 4.2.7 Aggregate expressions in the Postgres docs.

select   Content->>'Group' as "Group", 
         min(Content->>'When') filter (where Content->>'RecordNumber' = '0') "Start",
         max(Content->>'When') filter (where Content->>'RecordNumber' = '2147483647') "Stop",
         count(*)  as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

Check it here: http://rextester.com/ADYG46620

Removed UPDATE see http://meta.stackexchange.com/a/127655

Source Link

edited Apr 5, 2017 at 15:49

Paul White ♦

90.3k
30
424
663

Use a CASE statement to check if RecordNumber = 0 | 2147483647.

Try this version:

select   Content->>'Group' as "Group", 
         min(case when Content->>'RecordNumber' = '0' 
                  then Content->>'When' 
                  else null 
             end ) "Start",
         max(case when Content->>'RecordNumber' = '2147483647' 
                  then Content->>'When' 
                  else null 
             end ) "Stop",
         count(*) as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

The result:

| Group | Start      | Stop       | Count |
|-------|------------|------------|-------|
| 0     | 1490280300 | 1490280500 | 3     |
| 1     | 1490280600 | 1490280700 | 2     |
| 2     | 1490280900 | NULL       | 2     |

Update

As @EvanCarroll hasEvan Carroll pointed out, you can also take advantage of the FILTERFILTER clause. Have Have a look at 4.2.7 Aggregate expressions onin the Postgres docs.

select   Content->>'Group' as "Group", 
         min(Content->>'When') filter (where Content->>'RecordNumber' = '0') "Start",
         max(Content->>'When') filter (where Content->>'RecordNumber' = '2147483647') "Stop",
         count(*)  as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

Check it here: http://rextester.com/ADYG46620

Use a CASE statement to check if RecordNumber = 0 | 2147483647.

Try this version:

select   Content->>'Group' as "Group", 
         min(case when Content->>'RecordNumber' = '0' 
                  then Content->>'When' 
                  else null 
             end ) "Start",
         max(case when Content->>'RecordNumber' = '2147483647' 
                  then Content->>'When' 
                  else null 
             end ) "Stop",
         count(*) as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

The result:

| Group | Start      | Stop       | Count |
|-------|------------|------------|-------|
| 0     | 1490280300 | 1490280500 | 3     |
| 1     | 1490280600 | 1490280700 | 2     |
| 2     | 1490280900 | NULL       | 2     |

Update

As @EvanCarroll has pointed out, you can take advantage of the FILTER clause. Have a look at 4.2.7 Aggregate expressions on Postgres docs.

select   Content->>'Group' as "Group", 
         min(Content->>'When') filter (where Content->>'RecordNumber' = '0') "Start",
         max(Content->>'When') filter (where Content->>'RecordNumber' = '2147483647') "Stop",
         count(*)  as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

Check it here: http://rextester.com/ADYG46620

Use a CASE statement to check if RecordNumber = 0 | 2147483647.

Try this version:

select   Content->>'Group' as "Group", 
         min(case when Content->>'RecordNumber' = '0' 
                  then Content->>'When' 
                  else null 
             end ) "Start",
         max(case when Content->>'RecordNumber' = '2147483647' 
                  then Content->>'When' 
                  else null 
             end ) "Stop",
         count(*) as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

The result:

| Group | Start      | Stop       | Count |
|-------|------------|------------|-------|
| 0     | 1490280300 | 1490280500 | 3     |
| 1     | 1490280600 | 1490280700 | 2     |
| 2     | 1490280900 | NULL       | 2     |

As Evan Carroll pointed out, you can also take advantage of the FILTER clause. Have a look at 4.2.7 Aggregate expressions in the Postgres docs.

select   Content->>'Group' as "Group", 
         min(Content->>'When') filter (where Content->>'RecordNumber' = '0') "Start",
         max(Content->>'When') filter (where Content->>'RecordNumber' = '2147483647') "Stop",
         count(*)  as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

Check it here: http://rextester.com/ADYG46620

added 4 characters in body

Source Link

edited Mar 24, 2017 at 15:09

McNets

23.9k
11
50
89

Use a CASE statement to check if RecordNumber = 0 | 2147483647.

Try this version:

select   Content->>'Group' as "Group", 
         min(case when Content->>'RecordNumber' = '0' 
                  then Content->>'When' 
                  else null 
             end ) "Start",
         max(case when Content->>'RecordNumber' = '2147483647' 
                  then Content->>'When' 
                  else null 
             end ) "Stop",
         count(*) as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

The result:

| Group | Start      | Stop       | Count |
|-------|------------|------------|-------|
| 0     | 1490280300 | 1490280500 | 3     |
| 1     | 1490280600 | 1490280700 | 2     |
| 2     | 1490280900 | NULL       | 2     |

Check it here: http://rextester.com/UMPG54582

Update

As @EvanCarroll has pointed out, you can take advantage of the FILTER clause. Have a look at 4.2.7 Aggregate expressions on Postgres docs.

select   Content->>'Group' as "Group", 
         min(Content->>'When') filter (where Content->>'RecordNumber' = '0') "Start",
         max(Content->>'When') filter (where Content->>'RecordNumber' = '2147483647') "Stop",
         count(*)  as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

Check it here: http://rextester.com/ADYG46620

Use a CASE statement to check if RecordNumber = 0 | 2147483647.

Try this version:

select   Content->>'Group' as "Group", 
         min(case when Content->>'RecordNumber' = '0' 
                  then Content->>'When' 
                  else null 
             end ) "Start",
         max(case when Content->>'RecordNumber' = '2147483647' 
                  then Content->>'When' 
                  else null 
             end ) "Stop",
         count(*) as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

The result:

| Group | Start      | Stop       | Count |
|-------|------------|------------|-------|
| 0     | 1490280300 | 1490280500 | 3     |
| 1     | 1490280600 | 1490280700 | 2     |
| 2     | 1490280900 | NULL       | 2     |

Check it here: http://rextester.com/UMPG54582

Update

As @EvanCarroll has pointed out, you can take advantage of the FILTER clause. Have a look at 4.2.7 Aggregate expressions on Postgres docs.

select   Content->>'Group' as "Group", 
         min(Content->>'When') filter (where Content->>'RecordNumber' = '0') "Start",
         max(Content->>'When') filter (where Content->>'RecordNumber' = '2147483647') "Stop",
         count(*)  as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

Use a CASE statement to check if RecordNumber = 0 | 2147483647.

Try this version:

select   Content->>'Group' as "Group", 
         min(case when Content->>'RecordNumber' = '0' 
                  then Content->>'When' 
                  else null 
             end ) "Start",
         max(case when Content->>'RecordNumber' = '2147483647' 
                  then Content->>'When' 
                  else null 
             end ) "Stop",
         count(*) as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

The result:

| Group | Start      | Stop       | Count |
|-------|------------|------------|-------|
| 0     | 1490280300 | 1490280500 | 3     |
| 1     | 1490280600 | 1490280700 | 2     |
| 2     | 1490280900 | NULL       | 2     |

Update

As @EvanCarroll has pointed out, you can take advantage of the FILTER clause. Have a look at 4.2.7 Aggregate expressions on Postgres docs.

select   Content->>'Group' as "Group", 
         min(Content->>'When') filter (where Content->>'RecordNumber' = '0') "Start",
         max(Content->>'When') filter (where Content->>'RecordNumber' = '2147483647') "Stop",
         count(*)  as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

Check it here: http://rextester.com/ADYG46620

added 603 characters in body

Source Link

edited Mar 24, 2017 at 13:40

McNets

23.9k
11
50
89

Loading

added 71 characters in body

Source Link

edited Mar 23, 2017 at 17:04

McNets

23.9k
11
50
89

Loading

added 442 characters in body

Source Link

edited Mar 23, 2017 at 16:57

McNets

23.9k
11
50
89

Loading

added 99 characters in body

Source Link

edited Mar 23, 2017 at 16:48

McNets

23.9k
11
50
89

Loading

added 99 characters in body

Source Link

edited Mar 23, 2017 at 16:32

McNets

23.9k
11
50
89

Loading

added 126 characters in body

Source Link

edited Mar 23, 2017 at 16:22

McNets

23.9k
11
50
89

Loading

Source Link

created Mar 23, 2017 at 16:14

McNets

23.9k
11
50
89

Loading

Stack Exchange Network

Return to Answer