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:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
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

0
 
pdd1lanCommented:
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
0
 
IvanGarceteAuthor Commented:
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

0
 
IvanGarceteAuthor Commented:
It worked perfectly ralmada!
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.