Link to home
Create AccountLog in
Avatar of IvanGarcete
IvanGarceteFlag for Paraguay

asked on

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

Avatar of pdd1lan
pdd1lan

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
Avatar of IvanGarcete

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
It worked perfectly ralmada!
Thanks!