How can I make a left join in a temporal query-CodePudding

I have the following table:

char, id_1, id_2
a,100,50
a,100,50
a,100,50
b,101,50
b,101,50
c,200,51
c,200,51
d,201,51
e,202,52
e,202,52
e,202,52
e,202,52

I want to produce this output:

id_1, id_2, count, sum
100,50,3,5
101,50,2,5
200,51,2,3
201,51,1,3
202,52,4,4

A short explication:

I want to count the number of rows given the "id_1" and produce the column "count" then once I have the "count" column, I want to create the "sum" column by summing the groups of the id_2

I first try this:

select id_1, id_2, count(id_2) as count from myTable
group by id_1, id_2

to make the count column,

id_1, id_2, count
100,50,3
101,50,2
200,51,2
201,51,1
202,52,4

the problem is that I can't make the sum column.

Do you have any ideas? Thanks in advance.

CodePudding user response：

If you're using MySql 8 then window functions make this relatively simple:

select id_1, id_2, Count(*) "Count", Max(cnt) "sum"
from (
    select *, Count(*) over(partition by id_2) cnt
    from t
)t
group by id_1, id_2;

See demo Fiddle

CodePudding user response：

As Stu put it, window functions make this task a breeze. Here's my take on this task (By the time I finished Stu had already replied, might as well share mine anyway).

Schema (MySQL v8.0)

CREATE TABLE Test (
  `id_1` INTEGER,
  `id_2` INTEGER
);

INSERT INTO Test
  (`id_1`, `id_2`)
VALUES
  ('100', '50'),
  ('100', '50'),
  ('100', '50'),
  ('101', '50'),
  ('101', '50'),
  ('200', '51'),
  ('200', '51'),
  ('201', '51'),
  ('202', '52'),
  ('202', '52'),
  ('202', '52'),
  ('202', '52');

Query #1

select distinct 
*,
count(id_1) over (partition by id_1) as count,
count(id_2) over (partition by id_2) as sum
from Test;

id_1	id_2	count	sum
100	50	3	5
101	50	2	5
200	51	2	3
201	51	1	3
202	52	4	4

View on DB Fiddle