?
Solved

Stored Procedure to calculate sum

Posted on 2006-07-10
6
Medium Priority
?
864 Views
Last Modified: 2008-01-16
hi,

i am having trouble writing a stored function in MySQL 5 that will return the sum of a series of values. i wrote this so far

CREATE FUNCTION `Max_Peso`(IN tbl CHAR(64), IN col CHAR(64), OUT s INT) RETURNS INT
    READS SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'Sums all the maximum \'pesos\' grouped by category'
BEGIN
SET @s = CONCAT('SELECT SUM(MAX(' , col , ')) FROM ' , tbl, ' GROUP BY categoria');
PREPARE stmt FROM @s;
EXECUTE stmt;

RETURN s;
END;


but this is not working i get an error.
please help
0
Comment
Question by:newyuppie
  • 3
  • 3
6 Comments
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 17076066
Functions do not have in/out parameters.  If you intend to make this a function, drop the "IN" from the parameter list, and do not include the 's' parameter.  If you intend to make this a procedure, change "FUNCTION" to "PROCEDURE" and adjust the "return" accordingly.

Dynamic SQL is not allowed in stored functions.
0
 
LVL 13

Author Comment

by:newyuppie
ID: 17076178
thanks for the clarification. i now have this:

CREATE PROCEDURE `gr`(IN col VARCHAR, IN tbl VARCHAR)
    READS SQL DATA
    SQL SECURITY INVOKER
BEGIN
SET @s = CONCAT('SELECT SUM(MAX(' , col , ')) FROM ' , tbl, ' GROUP BY categoria');
PREPARE stmt FROM @s;
EXECUTE stmt;
END


and still get error. could you help me construct this procedure?
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 17076239
varchar fields must have a length specified, such as varchar(64)
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 13

Author Comment

by:newyuppie
ID: 17076250
ok UPDATE


i was missing in the IN col VARCHAR, missing to put (255) in the end. it now works, code is like this:
CREATE PROCEDURE `gr`(IN PCol VARCHAR(255), IN Ptbl VARCHAR(255))
    READS SQL DATA
    SQL SECURITY INVOKER
BEGIN
SET @s = CONCAT('SELECT MAX(' , PCol , ') FROM ' , PTbl, ' GROUP BY categoria');
PREPARE stmt FROM @s;
EXECUTE stmt;
DROP PREPARE stmt;
END


i get a list of the MAX values for each category. how do i SUM all these, and return the single summed value?
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 2000 total points
ID: 17076343
make your query this:
SET @s = CONCAT('SELECT SUM(theColumn) INTO s FROM (SELECT MAX(', PCol, ') AS theColumn FROM ', Ptbl, ' GROUP BY categoria) AS theValues');

and add your OUT s INT back in.
0
 
LVL 13

Author Comment

by:newyuppie
ID: 17079326
thanks

i removed the INTO s and excluded the OUT s INT from the declaration (because i kept getting errors). without these things, it works and returns a single row with the appropiate number,

thanks for your help!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 23 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question