Reputation: 1423
select SUM (Bill) from ProductSaleReport group by PCI
having MONTH(Date) between 1 and 3
Could any one please help me finding the issue.?
I am getting the errors:
Msg 8121, Level 16, State 1, Line 1
Column 'ProductSaleReport.Date' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8121, Level 16, State 1, Line 1
Column 'ProductSaleReport.Date' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Upvotes: 9
Views: 36566
Reputation: 621
Try this,
select SUM (Bill) from ProductSaleReport group by PCI having MIN(MONTH(Date))>=1 and MAX(MONTH(Date))<=3
Upvotes: 0
Reputation: 3950
MONTH(Date) is not a column you're grouped by, so it can't appear in having clause. You can do like that:
select SUM (Bill)
from ProductSaleReport
where MONTH(Date) between 1 and 3
group by PCI
Other way is
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 result grouped by month as well as by PCI.
The difference between WHERE and HAVING explained here: Using 'case expression column' in where clause
Upvotes: 15
Reputation: 10066
Use WHERE
to filter before group by
HAVING
is used to filter data after the group by occurs
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
Upvotes: 8
Reputation: 52372
MONTH(Date)
is not a column you SELECT
ed, so it can't appear in your HAVING
clause.
If you meant to only SUM
the Bill
s from rows where the month is between 1 and 3, then that is a WHERE
clause, not a HAVING
clause.
If all the rows in each PCI
group have the same MONTH(Date)
, then you can add MONTH(Date)
to your SELECT
clause in order to use it in the HAVING
clause.
Upvotes: 2