Creating a conditional SQL select statement that inserts based on a count in MYSQL

Question

I am trying to develop a statement that counts the sizes of a product and insert a statement base on the amount of that count.

For Example,

I have a shirt that has 3 sizes (S, M, L) For reach size I would need to insert a SQL statement. The insert statement is to insert on Table 2 the menu position.

Table1

ID |  Product | Size
1      Shirt      S  
2      Shirt      M
3      Shirt      L

Table2

ID  |  Dropdown_menu_Position
1        0
2        1
3        2

I know the following query is incorrect but I am struggling with the logic behind it. Any SQL guru can help solve or guide me in the right direction?

INSERT INTO Table2
CASE
WHEN COUNT (SIZE) = 1 THEN
     SELECT NULL, '0' 
WHEN COUNT (SIZE) = 2 THEN
     SELECT NULL, '1' 
WHEN COUNT (SIZE) = 3 THEN
     SELECT NULL, '2' 
ELSE ''
END

I am new at making CASE statement and maybe an IF statement would work better for this, but neither one I know.

First your query says count(size) = 1? But that is an ID not the size in your table structure. Looks like what you need is just a simple insert into table (column list) select columnes from table — Brad, Nov 08 '18 at 20:13
Can you give a more exact picture of what you're inserting where... I'm with @Brad5 I don't think a Case statement is what you're after at all... Maybe an INSERT INTO `YourSchema`.`YourTableName2` (`Col1`, `Col2`, `Col3`) SELECT FROM `YourTableName1`.... — DarbyM, Nov 08 '18 at 20:20

GolezTrol · Accepted Answer · 2018-11-08T20:32:32.403

IF you are using MySQL 8.0, you can use window functions for this. They can be used to generate a numbering based on a group:

insert into Table2(Id, Dropdown_menu_Position)
select
  t.ID,
  --t.Product,
  --t.Size,
  ROW_NUMBER() over (
    order by s.SortOrder
  ) partition by (t.Product) as Dropdown_menu_Position
from
  Table1 t
  inner join (
    -- Inlined now, but maybe you can make a lookup table for this
    select 'XS' as Size, 0 as SortOrder
    union all select 'S', 1
    union all select 'M', 2
    union all select 'L', 3
    union all select 'XL', 4
    union all select 'XXL', 5
  ) s on s.Size = t.Size

In earlier versions of MySQL, these functions don't exist, and this won't work. However, for this particular case, you should indeed be able to use count to check how many rows there are before the current one. If you have a separate table for the sizes, this query is quite readable, but if not, you would have to embed the lookup table twice in the query. The sample below assumed a separate table:

-- Assuming a lookup table like this:
create table Sizes as
  select 'XS' as Size, 0 as SortOrder
  union all select 'S', 1
  union all select 'M', 2
  union all select 'L', 3
  union all select 'XL', 4
  union all select 'XXL', 5;

-- Select all IDs and how often a smaller size exists for that product
insert into Table2(Id, Dropdown_menu_Position)
select
  t.ID,
  ( select count(*)
    from 
      Table1 t2
      inner join Sizes s2 on s2.Size = t2.Size 
    where
      t2.Product = t.Product and
      s2.SortOrder < s.SortOrder) as Dropdown_menu_Position
from
  Table1 t
  inner join Sizes s on s on s.Size = t.Size

But, maybe you don't need the numbers in Table2 to be consecutive. Maybe it's okay, as long as they have the right order (so M is below S in the menu). This will, of course, depend on your code, but if that is possible, you can simply write this:

insert into Table2(Id, Dropdown_menu_Position)
select
  t.ID,
  s.SortOrder
from
  Table1 t
  inner join (
    -- Inlined now, but maybe you can make a lookup table for this
    select 'XS' as Size, 0 as SortOrder
    union all select 'S', 1
    union all select 'M', 2
    union all select 'L', 3
    union all select 'XL', 4
    union all select 'XXL', 5
  ) s on s.Size = t.Size

another alternative according to [this](https://stackoverflow.com/a/1895127/10285235) would be: `select t1.ID as ID ,@rowid:=@rowid+1 as Dropdown_menu_Position from Table1 t1, (SELECT @rowid:=-1) x where t1.Product = 'Shirt'` — randomDude1001, Nov 08 '18 at 20:36
Yup, thanks for bringing that up. That's the "old" way around not having window functions. I would use that if strictly needed, but it doesn't have my preference in terms of readability. But maybe that's because I mostly work on different databases than MySQL. :-) — GolezTrol, Nov 08 '18 at 20:38

Creating a conditional SQL select statement that inserts based on a count in MYSQL

1 Answers1