How do I select data from a select query in MySql?

I am using MySQL 5.5. I have a query which returns results like this

quantity| maxPrice| minPrice |avgPrice| counts
"10"    "23.50"   "23.50"  "23.500000"  "1"
"15"    "15.75"   "15.75"  "15.750000"  "1"
"23"    "100.71"  "100.71" "100.710000" "1"
"25"    "210.00"  "200.00" "205.000000" "2"

now from this data I want to extract the maximum quantity tuple and sum of all counts... so that result would look like this

quantity| maxPrice| 开发者_如何学运维minPrice |avgPrice| counts
"25"    "210.00"  "200.00" "205.000000"  5

how do I write query for this?

Use ORDER BY quantity DESC and LIMIT 1 to extract the tuple. Surround your query with another select and use SUM(counts) AS counts for the total.

Example:

SELECT 
 x.quantity, x.maxPrice, x.minPrice, 
 x.avgPrice, SUM(x.counts) AS counts
FROM 
(
 SELECT * 
 FROM mytable
 ORDER BY quantity DESC
) AS x
LIMIT 1;

Replace SELECT * FROM mytable with your real query.

   SELECT 
     max(s.quantity) as quantity
     , max(s.maxPrice) as maxPrice
     , max(s.minPrice) as minPrice
     , max(s.AvgPrice) as avgPrice
     , max(s.totalcounts) as totalcounts
   FROM (
      SELECT t1.quantity,t1.maxPrice,t1.minPrice,t1.avgPrice, 0 as totalcounts
      FROM table1 t1
      ORDER BY quantity DESC 
      LIMIT 1
    UNION  
      SELECT 0,0,0,0,sum(t2.counts) as totalcounts
      FROM table1 t2
   ) s