Error with having clause

Question

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.

Well, what's the error? Though it doesn't look like you're using any aggregates - maybe you should be using WHERE instead? — Dmitri, Jul 27 '11 at 07:03
Read to get a better understanding of differences between WHERE and GROUP BY http://www.databasejournal.com/features/mysql/article.php/3469351/The-HAVING-and-GROUP-BY-SQL-clauses.htm — niktrs, Jul 27 '11 at 07:54

score 15 · Accepted Answer · edited May 23 '17 at 12:19

15

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

edited May 23 '17 at 12:19

Community

1
1

answered Jul 27 '11 at 07:06

Andrei Petrenko

3,922
3
31
53

1

The second version should be more efficient ! – iDevlop Jul 27 '11 at 07:14
@marc_s This sample code works for me: SELECT fiscalmonth,count(*) from inbalance where fiscalyear=2011 group by fiscalmonth having fiscalmonth between 1 and 3 – niktrs Jul 27 '11 at 07:15
1

@marc_s You see those "or the GROUP BY clause" words. If you group by MONTH(Date) you can use it in HAVING. – Andrei Petrenko Jul 27 '11 at 07:16
Pearl, both @Andrei wrote are working, beware that the use of having provides different results from that of the where clause because it is also grouping by month. – niktrs Jul 27 '11 at 07:50
@niktrs It seems to me results will be the same. Maybe I've missed something. Please give some more details. – Andrei Petrenko Jul 27 '11 at 07:53
@Andrei the first query will return PCI * MONTH number of rows since it's grouped by month and pci while the second will return PCI number of rows since it's grouped by only pci. – niktrs Jul 27 '11 at 07:57

score 8 · Answer 2 · edited Jul 27 '11 at 07:13

8

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

edited Jul 27 '11 at 07:13

marc_s

732,580
175
1,330
1,459

answered Jul 27 '11 at 07:06

niktrs

9,858
1
30
30

score 2 · Answer 3 · answered Jul 27 '11 at 07:02

2

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.

answered Jul 27 '11 at 07:02

Dan Grossman

51,866
10
112
101

SELECT has nothing to do with HAVING. Only GROUP BY matters. And aggregate functions, of course. Error message says the same. – Andrei Petrenko Jul 27 '11 at 07:07
I need to group the data based on PCI value. In the grop of PCI records, I need to find the sum of BILL Whose Bill date is on the month of jan,feb,march. – Shine Jul 27 '11 at 07:08
@Andrei Look at the example in the SQL Server manual... http://msdn.microsoft.com/en-us/library/ms180199.aspx . The `HAVING` clause operates on the result set, it doesn't really matter how the result set was produced, columns that weren't in the `GROUP BY` clause can be in that result set and used in the `HAVING` clause for filtering. – Dan Grossman Jul 27 '11 at 07:08
1

@Pearl Then that's a `WHERE` condition, not `HAVING`. Change `HAVING` to `WHERE` and move it before the `GROUP BY`. – Dan Grossman Jul 27 '11 at 07:10
There is aggregate function in where clause in http://msdn.microsoft.com/en-us/library/ms180199.aspx. – Andrei Petrenko Jul 27 '11 at 07:12
@Andrei Don't know what you're talking about, there are no `WHERE` clauses in any queries on that page. – Dan Grossman Jul 27 '11 at 07:13
Right, which is what I said. The `HAVING` clause is filtering on a column that's in the result set because it was `SELECT`ed. That column is not in the `GROUP BY` clause at all. – Dan Grossman Jul 27 '11 at 07:24
MONTH(Date) is not an aggregate function. COUNT and SUM are. – Andrei Petrenko Jul 27 '11 at 07:28
But What is the difference between filtering out with having and where? Why should we place where conditon before group by? – Shine Jul 27 '11 at 07:35
Please explain the above. I am new to SQL and learning it. That would be great help if any one explain me. – Shine Jul 27 '11 at 07:36
@Pearl Buy a book. The comments box is not the place to explain how a RDBMS evaluates all the clauses of a query. – Dan Grossman Jul 27 '11 at 07:38
1

@Pearl: short story: http://stackoverflow.com/questions/6545664/using-case-expression-column-in-where-clause/6545685#6545685 – ypercubeᵀᴹ Jul 27 '11 at 07:44

score 0 · Answer 4 · answered May 05 '21 at 14:27

0

Try this,

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

answered May 05 '21 at 14:27

rasika godawatte

601
5
2

Error with having clause

4 Answers4

Linked

Related