CODEWITHSUNDEEP
mysqldatabasequery-optimization
Dipen
Dipen

Reputation: 1064

mysql query optimization (using sum function )

I have a situation that i have used below query to retire usage of certain user but problem is that now mysql sums all the records from the table. Instead of getting sum of all records i just to want to stop query when the sum is greater than 500. I have tired 3 different ways. Can anyone suggest me which is the best way to achieve the result.

1st version query 

 SELECT
(SUM(radacct.acctinputoctets) + SUM(radacct.acctoutputoctets))/8388608 AS total_usage
FROM `radacct`
WHERE username ='fktmbishals'

2nd version query

SELECT
if ((SUM(radacct.acctinputoctets) + SUM(radacct.acctoutputoctets))/8388608 > 500   , 500, 0) AS total_usage
FROM `radacct`
WHERE username ='fktmbishals'

3rd version query

SELECT
(SUM(temp_radacct.acctinputoctets) + SUM(temp_radacct.acctoutputoctets))/8388608 AS total_usage
FROM (
   SELECT
radacct.acctinputoctets,radacct.acctoutputoctets
FROM `radacct`
WHERE username ='fktmbishals'
LIMIT 10
) as temp_radacct

Upvotes: 0

Views: 171

Answers (1)

Rick James
Rick James

Reputation: 142278

SET @tot := 0; 
SELECT  total_usage 
    FROM  
      ( SELECT  @usage := acctinputoctets + acctoutputoctets AS usage,
                @tot := @tot + @usage / 8388608 AS total_usage
            FROM  radacct
            WHERE username ='fktmbishals'
            ORDER BY  ?? 
      ) x
    HAVING  Total < 500;

That shows the running total until it exceeds 500. Is that what you wanted? Or did you want the last value? If so, put that inside another query that uses LIMIT 1.

What ORDER BY do you need?

Don't you need to know what items make up the 500?

What do you want if the entire total is less than 500?

Upvotes: 2

Related Questions