Create a stored procedure to populate a table with one row-Yearly and Monthly Averages

I have created a stored procedure to populate a log table. I am having trouble with the syntax for columns H and I. Is there something wrong with my syntax as it relates to monthly and yearly averages? My problem seems to be with those lines of code.

Output ROW Stored Procedure

   DELIMITER //
    CREATE PROCEDURE populate()
    BEGIN
    DECLARE NoEmpvar INT;
    DECLARE NoDeptvar INT; 
    DECLARE locregion1var INT; 
    DECLARE locregion2var INT; 
    DECLARE locregion3var INT; 
    DECLARE totalsalesvar DECIMAL(10,2); 
    DECLARE avgsalesvar DECIMAL(10,2); 
    DECLARE avgsalesmovar DECIMAL(10,2); 
    DECLARE avgsalesyrvar DECIMAL(10,2);
    
    SELECT COUNT(*) INTO NoEmpvar FROM  emp; 
    SELECT COUNT(*) INTO NoDeptvar FROM dept;
    SELECT COUNT(*) INTO locregion1var FROM location WHERE regionid=1; 
    SELECT COUNT(*) INTO locregion2var FROM location WHERE regionid=2; 
    SELECT COUNT(*) INTO locregion3var FROM location WHERE regionid=3; 
    SELECT COUNT(*) INTO totalsalesvar FROM sales;
    SELECT AVG(salesamt) INTO AvgSale FROM sales;
    SELECT AVG(salesamt) INTO avgsalesmovar FROM sales WHERE month(salesdate);
    SELECT AVG(salesamt) INTO avgsalesyrvar FROM sales WHERE year(salesdate);
INSERT INTO Log(NoEmp, NoDept, LocReg1, LocReg2, LocReg3, TotSales, AvgSale, AvgSaleMo, AvgSaleYr) 
    VALUES(NoEmpvar,NoDeptvar,locregion1var,locregion2var,locregion3var,totalsalesvar,avgsalesvar,avgsale smovar,avgsalesyrvar);
    END //

Answer

Maybe you dont have any sales in the sales table from this month or year? If you are confident that you do, maybe just run

select distinct month(salesdate) from sales

and also run

Select month(now())

and just see if there was anything different in your data?

Advertisement

Answer