PostgreSQL join subquery

Question

I have the following tables:

svc_sms:

sms_pk | sms_title

AND

svc_sms_msg:

msg_pk | sms_text | msg_status | sms_pk (FK to svc_sms.sms_pk)

I want to query and group all rows from svc_sms_msg by msg_status, so I do:

    SELECT sms.sms_pk, msg.msg_status, COUNT(msg.msg_status) as num
        FROM svc_sms_msg as msg
        INNER JOIN svc_sms as sms ON sms.sms_pk = msg.sms_pk
        GROUP BY sms.sms_pk, msg.msg_status
        ORDER BY sms.sms_pk, msg.msg_status

Now supose that msg.msg_status ranges ONLY between 0 and 4, so I need to get the number of times each sms with sms_status = 'x' appears in the grouping by sms_pk. I need my results to be something like this:

> sms.sms_pk  |  msg.msg_status  |  num 
> 
> 1                   0               1
> 
> 1                   1               5
> 
> 1                   2               4
> 
> 1                   3               20
> 
> 1                   4               18
> 
> 2                   0                5
> 
> 2                   1                0
> 
> 2                   2                3
> 
> 2                   3                23
> 
> 2                   4                0

But when there are not rows (in msg) with 'msg_status = x', the join gives me nothing, and I need to show the ammount of "statuses" even when they are 0's Before I was doing like this:

(SELECT SUM(CASE WHEN (msg_status = 0) THEN 1 ELSE 0 END) FROM svc_sms_msg WHERE sms_pk = svc_sms.sms_pk) as cnt_initial");
(SELECT SUM(CASE WHEN (msg_status = 1) THEN 1 ELSE 0 END) FROM svc_sms_msg WHERE sms_pk = svc_sms.sms_pk) as cnt_pending");
(SELECT SUM(CASE WHEN (msg_status = 2) THEN 1 ELSE 0 END) FROM svc_sms_msg WHERE sms_pk = svc_sms.sms_pk) as cnt_sended");

etc....but it proved to be too slow and unpractical in my case. How can I do this in a more eficient way? Thank you

Do you have a table which contains all possible msg status? if so cross join it to your 1st table then left join the result to your 2nd table. If not and you say only 0-4 then create a derived table unioning the 5 records together and cross join to your 1st table. — xQbert
– xQbert, Commented Feb 26, 2018 at 20:05

xQbert · Accepted Answer · 2018-02-26 20:16:37Z

2

You could create a derived table (All_Status below) of all status (0 to 4) and cross join that to your svc_sms then left join to svc_sms_msg

The cross join ensures every SVC_SMS has all statuses even if no message exists so the count will be zero for such records instead of missing. By left joining to svc_sms_msg on both the sms_pk and "status" we keep all the cross joined records ensuring every status is assigned to every SMS_PK; and thus counts will be 0 when msg.msg_status doesn't exist (is null)

SELECT sms.sms_pk, msg.msg_status, COUNT(msg.msg_status) as num
FROM svc_sms as sms 
CROSS JOIN (SELECT unnest(array[0,1,2,3,4]) status) All_Status
LEFT JOIN svc_sms_msg as msg
  ON sms.sms_pk = msg.sms_pk
 AND All_Status.Status = msg.msg_Status
GROUP BY sms.sms_pk, msg.msg_status
ORDER BY sms.sms_pk, msg.msg_status

Just depends if you want more columns for each status or a row for each status and sms_pk.

Total row count should be equal to 5 * # of SMS_Pk's given this approach.

edited Feb 26, 2018 at 20:16

answered Feb 26, 2018 at 20:10

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

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

3 Comments

Thorsten Kettner Over a year ago

Good answer. As there are obviously certain valid statuses that may be used in the svc_sms_msg table, it would be advisable to have a status table containing these. Then it would not be necessary to create such table on-the-fly in a query (which even had to be altered everytime a new status gets added).

xQbert Over a year ago

@ThorstenKettner Thanks! I agree if there is a source table listing all possible status, I would definitely pull from it (it's why my initial comment was to ask this. Baring a response I decided since they identified all statuses in the question they may not have such a source and thus this answer)

Pavarine Over a year ago

@xQbert I was not using a table for "status". Sorry for the delayed response. I've accepted khoroshevj answer cause I found it simpler to use this pgsql feature while it gaves me the efficiency required, but I understood your logic, thanks for the help!

khoroshevj · Accepted Answer · 2018-02-26 20:14:30Z

1

It can be made more simple due to cool postgresql feature FILTER

SELECT
    msg_pk
   ,COUNT() FILTER (WHERE msg_status = 0) as cnt_initial
   ,COUNT() FILTER (WHERE msg_status = 1) as cnt_pending
   ,COUNT() FILTER (WHERE msg_status = 2) as cnt_sended
FROM svc_sms_msg
GROUP BY sms_pk;

answered Feb 26, 2018 at 20:14

khoroshevj

1,13710 silver badges16 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2018-02-26 21:04:10Z

0

Perhaps conditional aggregation will work:

SELECT SUM(CASE WHEN (msg_status = 0) THEN 1 ELSE 0 END) as cnt_initial,
       SUM(CASE WHEN (msg_status = 1) THEN 1 ELSE 0 END) as cnt_pending;
       SUM(CASE WHEN (msg_status = 2) THEN 1 ELSE 0 END) ) as cnt_sended
FROM svc_sms_msg;

You can aggregate by a particular column . . . so you might want:

SELECT sms_pk, SUM(CASE WHEN (msg_status = 0) THEN 1 ELSE 0 END) as cnt_initial,
       SUM(CASE WHEN (msg_status = 1) THEN 1 ELSE 0 END) as cnt_pending;
       SUM(CASE WHEN (msg_status = 2) THEN 1 ELSE 0 END) ) as cnt_sended
FROM svc_sms_msg
GROUP BY sms_pk;

I find it a bit hard to follow the logic in your queries, but I think this is what you are trying to do.

edited Feb 26, 2018 at 21:04

answered Feb 26, 2018 at 20:08

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Tajinder · Accepted Answer · 2018-02-26 21:09:11Z

0

Hoping, I understood your question correctly.

Please check below query.

SELECT sms.sms_pk, msg.msg_status,
COUNT(CASE WHEN msg.msg_status='0' THEN 1 END) as num_0
COUNT(CASE WHEN msg.msg_status='1' THEN 1 END) as num_1
COUNT(CASE WHEN msg.msg_status='2' THEN 1 END) as num_2
COUNT(CASE WHEN msg.msg_status='3' THEN 1 END) as num_3
COUNT(CASE WHEN msg.msg_status='4' THEN 1 END) as num_4
            FROM svc_sms as sms 
            LEFT OUTER JOIN svc_sms_msg as msg ON sms.sms_pk = msg.sms_pk
            GROUP BY sms.sms_pk, msg.msg_status
            ORDER BY sms.sms_pk, msg.msg_status;

edited Feb 26, 2018 at 21:09

answered Feb 26, 2018 at 20:11

Tajinder

2,3484 gold badges38 silver badges58 bronze badges

1 Comment

Thorsten Kettner Over a year ago

Right joins are not considered very readable, so I'd advice to switch the tables and make this a left join.

Collectives™ on Stack Overflow

PostgreSQL join subquery

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related