Question 112936 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Creating a column in a table based on aggregation (without creating another table)

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.

Tags:

2

Answers


  1. 0 
    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

    Login or Signup to reply.
  2. 0

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

Back To Top
Search