ordering this statement

I have 开发者_StackOverflowthe following statement. The results come back but the records aren't sorted. What am I doing wrong?

SELECT * 
FROM (
SELECT name, date, SUM(volume) AS sumVolume, SUM(value) AS sumValue
FROM table
WHERE id = 12
) AS TempTable
ORDER BY date DESC

This query won't work as you intended. Using SUM (which is an AGGREGATE function) within your derived table is going to give you exactely ONE row back - unless you start to GROUP BY another column.

In your example, even if you'd group by id you'd still only get a single row, though (that's because you'd group by a single id). Alter the WHERE statement to include more ids and group them within the derived table.

Also, take a look at the MySQL manual on the various aggregate functions to get a feeling of what they do.

Regards

EDIT
Since the OP's requirements have somewhat become clear, I'll try to help him with his query:

SELECT x.name, x.date, y.vol, y.val
  FROM table AS x,
      ( SELECT MAX(date) AS date, SUM(volume) AS vol, SUM(value) AS val
          FROM table
         WHERE id = 12 ) AS y
 WHERE x.id = 12,
   AND x.date = y.date

I believe that this is what you are looking for:

SELECT * 
FROM (
  SELECT name, date, SUM(volume) AS sumVolume, SUM(value) AS sumValue
  FROM table
  WHERE id = 12
  GROUP BY date
) AS TempTable 
ORDER BY TempTable.date DESC

This will give you the sums of volume and value for each day, then order the results for you by day.

The previous version with no 'group by' doesn't give you sum by dates correctly in many circumstances.

Using ORDER BY TempTable.date in the outer select statement clarifies that what you want presented in date order is the results of the inner query.