I have a table of people transferring between positions:

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:

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?

CodePudding user response：

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

CodePudding user response：

@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');