问子查询foxpro

EN

* create test data
CREATE CURSOR test (id Int, category Int, price Num(15,2), amount Int, dateP D)
INSERT INTO test VALUES (1, 2, 24.25, 15, {^2014-10-5})
INSERT INTO test VALUES (2, 2, 700.0, 15, {^2014-7-25})
INSERT INTO test VALUES (3, 2, 110.10, 210, {^2015-11-15})
INSERT INTO test VALUES (4, 3, 110.10, 11, {^2014-11-15})

* your original 
SELECT category, SUM(price*amount) ;
    FROM test ;
    WHERE datep Between {^2014-01-01} And {^2014-12-31} ;
    GROUP BY category

* your desired result w/o sub-query
SELECT SUM(price*amount) ;
    FROM test ;
    WHERE datep Between {^2014-01-01} And {^2014-12-31} 

* the desired sub-query you described
SELECT SUM(Total) ;
    FROM ( ;
        SELECT category, SUM(price*amount) As Total ;
            FROM test ;
            WHERE datep Between {^2014-01-01} And {^2014-12-31} ;
            GROUP BY category ;
            ) As subQuery

* as per your comment
SELECT TOP 1 category, Max(Total) ;
    FROM ( ;
        SELECT category, SUM(price*amount) As Total ;
            FROM test ;
            WHERE datep Between {^2014-01-01} And {^2014-12-31} ;
            GROUP BY category ;
            ) As subQuery ;
    GROUP BY 1 ;
    ORDER BY 2 Desc

SELECT category,  
SUM(price*amount) AS Sum_Val;  
FROM dan2.dbf WHERE;  
between(date_p, {^2014-01-01}, {^2014-12-31});  
GROUP BY category  
ORDER BY 2 DESC  
INTO CURSOR TmpResults  

SELECT TmpResults  
GO TOP  
MaxSum = TmpResults.Sum_Val