0

I have this error: You tried to execute a query that does not include the specified expression 'Sales' as part of an aggregate function.

And my query is:

MyCommand.CommandText = "SELECT Sales, Created_Date FROM tblSales" & _
  "where Year(Created_Date)='" & year & "' and Month(Created_Date)='" & month & "' & _
   "GROUP BY Created_Date"

What does this mean and how to solve it?

Thanks in advance!

Matt Wilko
  • 26,994
  • 10
  • 93
  • 143
user3522506
  • 55
  • 7
  • 2
    Either add it to the group by or specifiy how you want to agregate `Sales`. You could for example take the `Max`-Sales for each date: `SELECT Max(Sales)AS MaxSales, Created_Date ...` – Tim Schmelter Sep 03 '14 at 10:12
  • Absolutely nothing to do with VB and everything to do with SQL 101 – Matt Wilko Sep 03 '14 at 13:05

1 Answers1

3

You either have to add it to the group by or you need to specifiy how you want to agregate Sales. Consider that it's possible that you get multiple rows per date. Then the database doesn't know which one you want. So you have to aggregate these rows(f.e. COUNT,SUM,MIN,MAX,..).

You could for example take the Max-Sales for each date: 

SELECT Max(Sales)AS MaxSales, Created_Date ...

It works also if you add it to the Group By but that could yield a different result since you could get multiple records per date.

SELECT Sales, Created_Date ...
... GROUP BY Sales, Created_Date
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • 2
    This is the perfect answer. Basically, the moment you even THINK about using GROUP BY, for every single column in the SELECT you must either perform an aggregation OR include it in the GROUP BY. – Dave Brown Sep 03 '14 at 12:46
  • Ah thanks a lot! So it's like compressing the rows and pointing to which one. – user3522506 Sep 03 '14 at 14:11
  • @user3522506: not exactly, it more like compressing the columns. You could _compress_ rows by joining with a subquery like in this answer: http://stackoverflow.com/a/3800572/284240 Understand a group as a set of records not a single record. For every column you can pick a different row's value for each group. – Tim Schmelter Sep 03 '14 at 14:21