Error when there is an offer

Question

select SUM (Bill) from ProductSaleReport group by PCI having MONTH(Date) between 1 and 3 

Can anyone help me find the problem.?

I get errors:

Msg 8121, Level 16, State 1, Line 1
The column "ProductSaleReport.Date" is invalid in the HAVING clause because it is neither contained in the aggregate function nor in the GROUP BY clause.
Msg 8121, Level 16, State 1, Line 1
The column "ProductSaleReport.Date" is invalid in the HAVING clause because it is neither contained in the aggregate function nor in the GROUP BY clause.

Answer 1

MONTH (Date) is not the column in which you are grouped, so it cannot appear in the declaration. You can do this:

 select SUM (Bill) from ProductSaleReport where MONTH(Date) between 1 and 3 group by PCI 

Another way -

 select SUM (Bill) from ProductSaleReport group by PCI, MONTH(Date) having MONTH(Date) between 1 and 3 

but keep in mind that you will get the result, grouped by month, as well as by PCI.

The difference between WHERE and HAVING is explained here: Using the case case column in the where section

Answer 2

Use WHERE to filter in front of a group.

HAVING used to filter data after a group through

 select SUM (Bill) -- comment: you need to add the PCI column since you use it in the group by right? from ProductSaleReport WHERE MONTH(Date) between 1 and 3 group by PCI 

Answer 3

MONTH(Date) not a column that you SELECT ed, so it cannot be displayed in your HAVING .

If you meant only SUM Bill from the lines where the month is between 1 and 3, then this is a WHERE , not a HAVING .

If all the rows in each PCI group have the same MONTH(Date) , then you can add MONTH(Date) to your SELECT to use it in the HAVING .