There are several problems with your query, some of those problems also exist with the query in Solution 3.
1. Avoid the use of reserved words for column names. If you do use a reserved word then you should surround the column name with square brackets so column
date should be referred to as
[date]
2. Similarly, column names should not have spaces in them, they too must be surrounded by square brackets i.e.
invoice no should be
[invoice no]
3. The order of the clauses in your query is incorrect - the
WHERE clause comes
before the
GROUP BY clause, not after.
4. If you are going to use sub-queries then each of the items returned must be given an alias name in order to be included in the outer query e.g. (but see below)
select sum(amount) AS Amt from employees group by invoice no
5. Also on the subject of sub-queries, if you are going to use them then you need to retrieve
all of the items you later try to refer to i.e.
id,
empname, etc
6. You do not need a sub-query for this.
7. When using dates it is better to use the less ambiguous format of YYYY-MM-DD. We can't tell if your date is MM-DD-YYYY or DD-MM-YYYY, and this can cause issues when trying to display or work with dates in an application. The date should be presented as
'1992-01-01'
Taking all of the above into account, but with the caveat that you have not shown us your table structure, any sample data, or expected results, then this query should work
select id,[date], empname,[invoice no],sum(amount) as Amount from employees
where [date]>= '1992-01-01'
group by id,[date],empname,[invoice no]
Submit an article or tip