Solved

Stored Procedure to calculate sum

Posted on 2006-07-10
6
853 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
insert row field data graphically 4 39
Select Query - Group By Function Producing Unexpected Results 8 39
MySQL recovery 7 57
MySQL Persistent Connections 10 54
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

732 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