Invaild use of group function

I am trying to make an after insert trigger that will add the bnumber to the table available with the rack-id that has the most open slots. To get this I need to take the total number of slots – the number of instances and if there are no free slots then the insert will fail. Below is what I have came up with so far but I don not know if it is at all correct and right now it throws 1111: Invalid use of Group function.

 BEGIN
    insert into 
    Available
    SELECT new.bnumber,max('y.rack-id')  
    From (select count(*) as 'Available.rack-id'
    from Available inner join Rack
    group by 'Available.rack-id'
    ) y
    where max(y.rack-id) > 0;
    
    END

updated non error code but is not inserting anything into the table Available

insert into Available
SELECT new.bnumber,max('y.rack-id')  
From 
(select count(*) as 'Available.rack-id'
from Available inner join Rack on 'Rack.rack-id' = 'Available.rack-id'
group by 'Available.rack-id'
) y
having max('y.rack-id') > 0;

Tables:

CREATE TABLE `Bike` (
  `bnumber` int NOT NULL,
  `make` varchar(64) DEFAULT NULL,
  `color` varchar(8) DEFAULT NULL,
  `year` int DEFAULT NULL,
  PRIMARY KEY (`bnumber`)
)

CREATE TABLE `Rack` (
  `id` int NOT NULL,
  `location` varchar(256) DEFAULT NULL,
  `num-holds` int DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `Available` (
  `bnumber` int NOT NULL,
  `rack-id` int DEFAULT NULL,
  PRIMARY KEY (`bnumber`),
  KEY `bnumber_idx` (`rack-id`),
  KEY `bnumber_idx1` (`bnumber`),
  CONSTRAINT `bnumber` FOREIGN KEY (`bnumber`) REFERENCES `Bike` (`bnumber`),
  CONSTRAINT `rack-id` FOREIGN KEY (`rack-id`) REFERENCES `Rack` (`id`)
) 

Bike
bnumber,make,color,year
'1', muller, yellow, 2017
'2', muller, green, 2018
'3', muller, red, 2020

Rack
id,location,num-holds  (num-holds is the number of empty slots)
'0', 'Louisville', '3'
'1', 'Clarksville', '4'

Available
bnumber,rack-id
'1', '0'
'2', '0'
'3', '1'

so in this examples when you add a new bike it will be something like bnumber 4. It will then get the count of instances of rack-id so 2 instances of rack-id 0 and 1 instance of rack-id 1. Then it will subtract the the instances from the corresponding num-holds. so this step would be rack-id 0 has 3 num-holds - 2 instances = 1 empty slot available. rack-id 1 has 4 num-holds - 1 instance = 3 empty slots, so bnumber 4 would be inserted into rack with the rack-id 1.

Answer

First, you are missing a ON clause:

from Available 
inner join Rack

it will look something like this:

ON Available.col = Racl.col

Then, in your outer query:

insert into Available
SELECT new.bnumber
      ,max('y.rack-id')  
From 
(
    
) y
where max(y.rack-id) > 0;

You are missing a GROUP BY new.bnumber and also HAVING clause is use instead WHERE when you need to filter by aggregation.

DECLARE rowcount INT;

select count(*)
INTO rowcount
from Available inner join Rack 
    on 'Rack.rack-id' = 'Available.rack-id'
group by 'Available.rack-id'
ORDER BY 'rack-id'
LIMIT 1;


insert into Available ('bnumber','rack-id')
SELECT new.bnumber, rowcount

Advertisement

Answer