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

MySQL Server

Last Comment

IvanGarcete

8/22/2022 - Mon

pdd1lan

3/4/2011

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

IvanGarcete

3/4/2011

ASKER

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