PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to Create SQL Query to Count Status Values Grouped by Remarks

I have two tables, master and Details, and I need to generate a report that counts the occurrences of each status value for each remark.

Here’s the structure of my tables:

Table: master

m_id (Primary Key)
remarks (TEXT)

Table: Details

d_id
m_id (foreign key referencing master.m_id)
item
status

Here are some sample data inserts:

-- Inserting into master table
INSERT INTO master(m_id, remarks) VALUES(1, 'Remarks1');
INSERT INTO master(m_id, remarks) VALUES(2, 'Remarks2');

-- Inserting into Details table
INSERT INTO Details(d_id, m_id, item, status) VALUES(1, 1, 'Item1', 1);
INSERT INTO Details(d_id, m_id, item, status) VALUES(2, 1, 'Item2', 2);
INSERT INTO Details(d_id, m_id, item, status) VALUES(3, 1, 'Item3', 2);
INSERT INTO Details(d_id, m_id, item, status) VALUES(4, 1, 'Item3', 3);

INSERT INTO Details(d_id, m_id, item, status) VALUES(5, 2, 'Item1', 3);
INSERT INTO Details(d_id, m_id, item, status) VALUES(6, 2, 'Item2', 3);
INSERT INTO Details(d_id, m_id, item, status) VALUES(7, 2, 'Item3', 2);

Desired output:

Remarks Status1_count Status2_count Status3_count
Remarks1 1 2 1
Remarks2 0 1 2

Tried using CASE. But here column name is dynamic.

Tags:

2

Answers


  1. 0

    You could use conditional aggregation…

    --    S Q L :
Select      m.remarks, 
            Count(CASE WHEN d.status = 1 THEN 1 END) AS status1_cnt,
            Count(CASE WHEN d.status = 2 THEN 1 END) AS status2_cnt,
            Count(CASE WHEN d.status = 3 THEN 1 END) AS status3_cnt
From        master m
Inner Join details d ON( d.m_id = m.m_id )
Group By    m.remarks

    /*    R e s u l t : 
remarks   status1_cnt  status2_cnt  status3_cnt
--------  -----------  -----------  -----------
Remarks1            1            2            1
Remarks2            0            1            2    */

    See the fiddle here.

    Login or Signup to reply.
  2. 0

    I assume that the number of status values may vary and become quite big. So I would suggest dynamic approach with a single JSON column per remarks with StatusX_count as attributes.

    select remarks, json_object_agg(stcnt, cnt)
from
(
  select remarks, format('Status%s_count', status) stcnt, count(*) cnt
  from master join details using (m_id)
  group by remarks, status
) t
group by remarks;
    remarks status_count
    Remarks1 { "Status1_count" : 1, "Status3_count" : 1, "Status2_count" : 2 }
    Remarks2 { "Status3_count" : 2, "Status2_count" : 1 }

    DB-Fiddle demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search