CODEWITHSUNDEEP
sqlpostgresql
Dhruv Pahuja
Dhruv Pahuja

Reputation: 135

How to order the query result based on following condition?

Let's suppose I have a table as below:

employee | period  | commission
A        | 1Y      | 100
A        | 2Y      | 150
B        | 1Y      | 80
C        | 1Y      | 200
C        | 2Y      | 270
C        | 6M      | 80

this data shows the commission earned by employees based on time period. now, I need to get the data in the following format in node.js code

[
  {
    "employee": "A",
    "commission": [
      {
        "period": "1Y",
        "commission": 100
      },
      {
        "period": "2Y",
        "commission": 150
      }
    ]
  },
  {
    "employee": "B",
    "commission": [
      {
        "period": "1Y",
        "commission": 80
      }
    ]
  },
  {
    "employee": "C",
    "commission": [
      {
        "period": "6M",
        "commission": 80
      },
      {
        "period": "1Y",
        "commission": 200
      },
      {
        "period": "2Y",
        "commission": 270
      }
    ]
  }
]

I have written following query 

select 
  employee, 
  json_agg (json_build_object('period', period, 'commission', commission)) as commission 
from 
  table_name 
  group by employee

This query gave me the desired result, but now I also want to sort the employees based on commission earned in a specific period e.g. sort employees based on the commission earned in period='1Y'. I am unable to find a solution with this query. Please provide any other good solution for these kinds of problems

Upvotes: 1

Views: 64

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

I would express this as:

select employee, 
       json_agg(json_build_object('period', period, 'commission', commission)) as commission 
from table_name 
group by employee
order by sum(commission) filter (where period = '1Y');

No subquery is necessary.

Upvotes: 1

klin
klin

Reputation: 121524

Extract the one year commission in your query and use it in the wrapper query to sort the results:

select employee, commission
from (
    select 
        employee, 
        json_agg (json_build_object('period', period, 'commission', commission)) as commission,
        max(case period when '1Y' then commission end) as one_year_commission
    from table_name 
    group by employee
    ) s
order by one_year_commission

Upvotes: 1

Related Questions