I have a big (4 milj rows) aggregation table based on month, prod, customer,
country etc columns with month Qty totals, always queried by month with
really good performace ! I also have a small correction transaction table (<
5000 rows) that is not aggregated. I want to query the union of those and
because of filters the result is always small, seldom more than 100 rows.
Performance must not be much slower than to the aggregation table alone.

Is possible to do a query like this ? I get error messages from Query
analyzer and don't understand BOL's SELECT/UNION syntax. (I'm not able to
update the aggregation table with data from the correction table).

---
select distinct month, prod, sum(qty)
from (
    select distinct month, prod, sum(qty)
    from BIG_AGGREGGATION_TABLE
    where month = 'aug 2005' and prod between 'A1000' and 'A1009'
    group by month, prod

    union all

    select distinct month, prod, sum(qty)
    from SMALL_CORRECTIONS_TABLE
    where month = 'aug 2005' and prod between 'A1000' and 'A1009'
    group by month, prod
)
order by month, prod
group by month, prod

=?Utf-8?B?L2plcm9tZSBr?= (jero***@discussions.microsoft.com) writes:
Show quoteHide quoteThere are at least three syntax errors in this query:

1) There is no alias for the derived table.
2) There is no column name for the third column of the derived table.
3) ORDER BY comes before the GROUP BY.

All your DISTINCT are superfluous. There is also no reason to aggredate
in the derived table, but just say:

  select month, prod, sum(qty)
  from (
      select month, prod, qty
      from BIG_AGGREGGATION_TABLE
      where month = 'aug 2005' and prod between 'A1000' and 'A1009'
     union all
      select month, prod, qty
      from SMALL_CORRECTIONS_TABLE
      where month = 'aug 2005' and prod between 'A1000' and 'A1009'
  ) AS x
  group by month, prod
  order by month, prod

Find duplicates
When was a database accessed last
Good or Bad practice?
substring of char field
Check if files exist Stored Proc