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

Multiple Counts in Postgresql Query

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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    @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');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search