'How can I make a left join in a temporal query

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.

mysqlsqlmysql-workbench


Solution 1:[1]

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

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stu
Solution 2 Tom Ellyatt

Related Questions