I've created a simple UPDATE query as shown below
SET Number = Sum([Count])
I get the following error message appear
An aggregate may not appear in the set list of an UPDATE statement.
This is what is in each tables...
SELECT * FROM Table1
SELECT * FROM Table2
And I want the Sum of all the Table2.Count placed in the Number column of Table1...
How can i do this?
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats.
Code should be in Standard SQL as much as possible and not local dialect. What you posted is 1970's Sybase dialect and it has cardinality problems (Google it). We use MERGE in the 21-st century :)
COUNT and SUM are aggregate functions, so of course you cannot nest them! Why do you think that “number” is a precise, clear data element name that will make your code easy to maintain?
Your posting makes no sense. There are no keys; hours is a unit of measurement not an identifier! UPDATE is a statement, not a query, etc.
If you are using using Table2 (awful name!) to stage data for Table1, then it mighty look like this:
MERGE INTO Table1
USING (SELECT foobar_class, COUNT(*)
GROUP BY foobar_class)
AS Source(foobar_class, foobar_cnt)
ON Table1.foobar_class = Table2.foobar_class
THEN UPDATE Table1
= foobar_grand_cnt + Source.foobar_cnt
WHEN NOT MATCHED
THEN UPDATE Table1
= foobar_grand_tot + Source.foobar_cnt;
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL