MySQL Server
MySQL Server

How to make a Store Procedure that sums in MySQL

Hello, I need to build a store procedure that sums the results of a query. I need to make a sum of "SubTotal" and return it. How can I do this?

Thanks.

SELECT 
  assignment.TskCod,
  task.TskHHP,
  IF(AssType = 'install', task.TskHHP * task.TskI_Percentage, task.TskHHP * task.TskR_Percentage) AS SubTotal
FROM
  assignment
  INNER JOIN task ON (assignment.TskCod = task.TskCod)
WHERE
  AssDate >= '2011-01-01' AND 
  AssDate <= '2011-01-31'

Open in new window

IvanGarceteAsked: 2011-03-04

Who is Participating?

Solutions

LVL 41

ralmadaConnect With a Mentor Commented: 2011-03-04

WITH is a MS SQL command, not MySQL

Try like below.

If not please post some sample data and the expected result.

SELECT 
		sum(IF(AssType = 'install', task.TskHHP * task.TskI_Percentage, task.TskHHP * task.TskR_Percentage))
	FROM assignment
	INNER JOIN task ON (assignment.TskCod = task.TskCod)
	WHERE 	AssDate >= '2011-01-01' AND 
		AssDate <= '2011-01-31'

Open in new window

LVL 8

pdd1lanCommented: 2011-03-04

here is an example:

CREATE PROCEDURE store_procedure_name

AS
BEGIN

SET NOCOUNT ON;

WITH A AS
(
SELECT field1, field2
from table1
where some condition
)

SELECT sum(field1) as mytotal
FROM a
group by field2

END
GO

IvanGarceteAuthor Commented: 2011-03-04

I've got this following your example. SET NOCOUNT ON didn't work, and then it didn't like WITH a AS either. What can be the problem?

Thanks.

BEGIN
       /* It did not recognize this, so I comment it */
	/*SET NOCOUNT ON; */
    
    WITH a AS(
		SELECT 
			IF(AssType = 'install', task.TskHHP * task.TskI_Percentage, task.TskHHP * task.TskR_Percentage) as SubTotal
		FROM
			assignment
			INNER JOIN task ON (assignment.TskCod = task.TskCod)
		WHERE
			AssDate >= '2011-01-01' AND 
			AssDate <= '2011-01-31';
    )
    
    SELECT SUM(SubTotal)
    FROM a;
END