Solved

Stored Procedure to calculate sum

Posted on 2006-07-10
6
845 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Constructing a Query Using Escaped Variables 3 42
count download link and run update query 9 55
MySQL - Restore Database SQL File 5 41
Mysql sync between 3-4 mysql db 4 23
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Creating and Managing Databases with phpMyAdmin in cPanel.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now