Shine
Shine

Reputation: 1423

Error with having clause

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

Answers (4)

rasika godawatte
rasika godawatte

Reputation: 621

Try this,

select SUM (Bill) from ProductSaleReport group by PCI having MIN(MONTH(Date))>=1 and MAX(MONTH(Date))<=3

Upvotes: 0

Andrei Petrenko
Andrei Petrenko

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

niktrs
niktrs

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

Dan Grossman
Dan Grossman

Reputation: 52372

MONTH(Date) is not a column you SELECTed, so it can't appear in your HAVING clause.

If you meant to only SUM the Bills 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

Related Questions