Mysql optimization, muliple select to single select

Question

hi,

I have table where I need to select MAX(SUM(total)) of a periods total.

SELECT SUM(P.amount) as total FROM bank P WHERE P.ReceivedDate >= '2008-07-28' AND P.ReceivedDate <= '2008-08-31';

SELECT SUM(P.amount) as total FROM bank P WHERE P.ReceivedDate >= '2008-09-01' AND P.ReceivedDate <= '2008-09-28';

SELECT SUM(P.amount) as total FROM bank P WHERE P.ReceivedDate >= '2008-09-29' AND P.ReceivedDate <= '2008-10-26';

I have run the above queries and find the Highest total in php.

I am not sure how to group by this query.

I am expecting a single query to replace all this query to MAX(SUM(total)).

please help me out.

Answer 1

You could try something like

SELECT  TOP 1
        CASE
            WHEN P.ReceivedDate BETWEEN '2008-07-28' AND '2008-08-31' THEN 1
            WHEN P.ReceivedDate BETWEEN '2008-09-01' AND '2008-09-28' THEN 2
            WHEN P.ReceivedDate BETWEEN '2008-09-29' AND '2008-10-26' THEN 3
        END,
        SUM(P.amount) Total
FROM    bank p
GROUP BY CASE
            WHEN P.ReceivedDate BETWEEN '2008-07-28' AND '2008-08-31' THEN 1
            WHEN P.ReceivedDate BETWEEN '2008-09-01' AND '2008-09-28' THEN 2
            WHEN P.ReceivedDate BETWEEN '2008-09-29' AND '2008-10-26' THEN 3
        END

HAVING  CASE 
            WHEN P.ReceivedDate BETWEEN '2008-07-28' AND '2008-08-31' THEN 1 
            WHEN P.ReceivedDate BETWEEN '2008-09-01' AND '2008-09-28' THEN 2 
            WHEN P.ReceivedDate BETWEEN '2008-09-29' AND '2008-10-26' THEN 3 
        END IS NOT NULL
ORDER BY Total DESC

or a shorter version

SELECT  TOP 1
        SUM(amount) Total
FROM    (
            SELECT  CASE 
                        WHEN P.ReceivedDate BETWEEN '2008-07-28' AND '2008-08-31' THEN 1 
                        WHEN P.ReceivedDate BETWEEN '2008-09-01' AND '2008-09-28' THEN 2 
                        WHEN P.ReceivedDate BETWEEN '2008-09-29' AND '2008-10-26' THEN 3 
                    END GroupVal,
                    P.amount
            FROM    bank P
        ) Vals
GROUP BY Vals.GroupVal
HAVING  Vals.GroupVal IS NOT NULL
ORDER BY Total DESC