CODEWITHSUNDEEP
postgresql
harsh atal
harsh atal

Reputation: 409

Aggregating columns and getting count of values as row

I have a query output as below: 

ID      ID2      Working Leave  Off Day
14595   76885302    10     0       0
178489  78756208    0      0       1
178489  78756208    0      1       0

I want to receive an output like below:

ID      ID2          code    value 
14595   76885302    Working    10     
178489  78756208    Off day    1      
178489  78756208    Leave      1

My query is like below:

select tei.organisationunitid,pi.trackedentityinstanceid as tei,
count(case when tedv.value = 'Working' then tedv.value end) Working,
count(case when tedv.value = 'Off day' then tedv.value end) Offday,
count(case when tedv.value = 'Leave' then tedv.value end) Leave
    from programstageinstance psi
    inner join programinstance pi on pi.programinstanceid = psi.programinstanceid
    inner join trackedentitydatavalue tedv on tedv.programstageinstanceid = psi.programstageinstanceid
    inner join dataelement de on de.dataelementid = tedv.dataelementid
    inner join trackedentityinstance tei on tei.trackedentityinstanceid = pi.trackedentityinstanceid
    where psi.executiondate between '2017-01-01' and '2019-06-01'
    and de.uid in ('x2222EGfY4K')
    and psi.programstageid in (select programstageid 
                                from programstage 
                                where uid = 'CLoZpO22228')
    and tei.organisationunitid in (select organisationunitid 
                                    from organisationunit 
                                    where path like '%Spd2222fvPr%')
    group by pi.trackedentityinstanceid,de.uid,tei.organisationunitid,tedv.value

How can I achieve this?

Upvotes: 0

Views: 46

Answers (1)

S-Man
S-Man

Reputation: 23676

I would try the JSON approach. I made a step-by-step fiddle:

demo:db<>fiddle

SELECT 
    id, id2,
    elements ->> 'code' AS code,
    SUM((elements ->> 'value')::int) AS value
FROM (
    SELECT 
        id,
        id2,
        json_build_object('code', 'working', 'value', working) AS working, 
        json_build_object('code', 'leave', 'value', leave) AS leave, 
        json_build_object('code', 'off_day', 'value', off_day) AS off_day
    FROM
        mytable
) s,
unnest(ARRAY[working, leave, off_day]) as elements
GROUP BY 1,2,3
HAVING SUM((elements ->> 'value')::int) > 0

Upvotes: 2

Related Questions