Multiple Counts in Postgresql Query

Question

I have a table of people transferring between positions:

id	new_position	old_position
1	A	B
2	C	B
3	B	D

And so on, for several hundred rows.

I want to make a query that will return each position with the number of people that transferred into that position, and the number of people that transferred out. The result looking like:

position	in	out
A	12	15
B	5	20
C	23	5

Etc.

It's easy to get a count for either in, or out:

SELECT new_position, count(*)
FROM transfers
GROUP BY new_position
ORDER BY new_position

I don't know how to get both into a single query though. Any suggestions?

Serg · Accepted Answer · 2022-09-01 19:16:35Z

3

You can use LATERAL

select t.pos, sum(t.cin) in, sum(t.cout) out
from mytable,
    lateral  (
      values 
          (new_position, 1 ,0), 
          (old_position, 0 ,1)
    ) t(pos, cin, cout)
group by t.pos
order by t.pos

answered Sep 1, 2022 at 19:16

Serg

22.8k5 gold badges23 silver badges50 bronze badges

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

1 Comment

Khantahr Over a year ago

Thank you! I wasn't aware of lateral!

Marco Luzzara · Accepted Answer · 2022-09-01 20:31:06Z

@Serg solution is very good and should be the accepted one, here is another one with simple SQL statements:

WITH aggr_new as (
  SELECT `new_position`, COUNT(*) as pos_in
  FROM `positions`
  GROUP BY `new_position`
), 
aggr_old as (
  SELECT `old_position`, COUNT(*) as pos_out
  FROM `positions`
  GROUP BY `old_position`
)
SELECT IFNULL(t.`new_position`, t.`old_position`) as `position`, IFNULL(t.pos_in, 0) pos_in, IFNULL(t.pos_out, 0) pos_out
FROM (
  SELECT *
  FROM aggr_new n
  LEFT OUTER JOIN aggr_old o ON n.`new_position` = o.`old_position`
  UNION
  SELECT *
  FROM aggr_new n
  RIGHT OUTER JOIN aggr_old o ON n.`new_position` = o.`old_position`
) t

The first 2 CTE are based on your initial idea to count the positions separately, but then you can join them with a FULL OUTER JOIN (that is emulated with a UNION of left and right joins). In this way, you are sure that all the positions are present. NULL values are replaced with 0 for pos_in and pos_out when not present in the full join.

Tested on:

CREATE TABLE IF NOT EXISTS `positions` (
  `id` int(6) unsigned NOT NULL,
  `new_position` char(1) NOT NULL,
  `old_position` char(1) NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `positions` (`id`, `new_position`, `old_position`) VALUES
  (1, 'A', 'B'),
  (2, 'B', 'C'),
  (3, 'A', 'C'),
  (4, 'C', 'B'),
  (5, 'A', 'B');

Thanks! I had figured out most of this excerpt for the IFNULL bit!

Collectives™ on Stack Overflow

Multiple Counts in Postgresql Query

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related