I'm trying to display a "top 10" list of accounts that have generated the most revenue. The data comes from two different tables (current and archived).
Here is the code I'm using.
SELECT id, SUM(amount) AS total_amount FROM ((SELECT id, amount FROM current where code >= '$start_date' and code <= '$end_date') UNION (SELECT id, SUM(amount) as total_amount FROM archived where code >= '$start_date' and code <= '$end_date')) as x GROUP BY id ORDER BY total_amount DESC 10
The start_date and end_date are defined and works from a PHP time() stamp. That part of the query isn't causing trouble. I'm just not getting the expected results with the above query where the total_amount is concerned. I think it has to do with "as x" or the ORDER BY but not sure. I haven't worked with UNIONS before. I tried ORDER BY x but got an error. I assumed x contained the total for both queries.
Output should read something like this:
Account 1: $500.00
Account 2: $450.00
Account 3: $400.00
etc.
And the total amounts again come from the two tables, current and archived.
