Solved

Stored Procedure to calculate sum

Posted on 2006-07-10
6
855 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
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.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

726 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