Creating a column in a table based on aggregation (without creating another table)-CodePudding

I have a table called Sets for LEGO:

set_number (Primary Key)	set_name	other_fields
123	Firetruck	abc
234	Star Wars	abc

I have another table called Parts for LEGO:

part_number (Primary Key)	name	set_number (references set_number from Sets)
1	Truck Roof	123
2	Truck Body	123
3	Neimoidian Viceroy Robe	234

I want to create another column in the Sets table to indicate the number of unique parts the particular set has.

I was able to output the number of unique parts with the following:

SELECT s.set_number, COUNT(*) AS num_diff_parts
FROM Sets s, Parts p
WHERE p.set_number = s.set_number
GROUP BY s.set_number

This outputs the following table (let's call it results):

set_number	num_diff_parts
123	2
234	1

However, I wonder if I can put the column (num_diff_parts) into the Sets table as a new column, instead of having to run this query every time when I need this information, or create another table just to contain the content of the results table.

Ideally, the Sets table should look like this:

set_number (Primary Key)	set_name	other_fields	num_diff_parts
123	Firetruck	abc	2
234	Star Wars	abc	1

I've also tried to do GROUP BY on multiple fields, but I don't think that's safe to do as those fields can have repeats and will throw off the results.

CodePudding user response：

select  distinct 
        set_number
       ,set_name
       ,other_fields
       ,count(*) over(partition by set_number) as num_diff_parts
from    Sets join Parts using(set_number)

set_number	set_name	other_fields	num_diff_parts
123	Firetruck	abc	2
234	Star Wars	abc	1

We can also count() before joining the tables.

with parts_cnt as (
                  select   set_number
                          ,count(*) as num_diff_parts
                  from     Parts
                  group by set_number
                  )
select  *
from    Sets join parts_cnt using(set_number)

Fiddle

CodePudding user response：

However, I wonder if I can put the column (num_diff_parts) into the Sets table as a new column, instead of having to run this query every time when I need this information

I would recommend using a view ; with this technique, the information is always available, and you don’t need to keep it up to date by yourself.

In MySQL, a correlated subquery comes handy to efficiently compute the count of parts per set :

create view v_sets as
select s.*, 
    (
        select count(*)
        from parts p
        where p.set_number = s.set_number
    ) num_diff_parts
from sets s