SQL Server query having clause

Question

In below query I am getting error after HAVING clause. Any help will be appreciated. Thanks in advance.

Select round(TaxableAmount,0) as t, round(((TaxValue*100)/TaxPercentage),0)as t2 from VoucherTaxDetail where 
TaxPercentage<>0 and TaxableAmount>0 and TableName in('TVPayment','Payment') group by transactionId having t<>t2

SELECT ROUND(TAXABLEAMOUNT, 0) AS T, ROUND(( ( TAXVALUE * 100 ) / TAXPERCENTAGE ), 0)AS T2 FROM VOUCHERTAXDETAIL WHERE TAXPERCENTAGE <> 0 AND TAXABLEAMOUNT > 0 AND TABLENAME IN( 'TVPAYMENT', 'PAYMENT' ) GROUP BY TRANSACTIONID HAVING ROUND(TAXABLEAMOUNT, 0) <> ROUND(( ( TAXVALUE * 100 ) / TAXPERCENTAGE ),0) — Chanukya, Sep 17 '18 at 11:49
@Chanukya please delete the comment and post it as an answer explaining to the PO what you did — Simo, Sep 17 '18 at 11:50

score 2 · Answer 1 · answered Sep 17 '18 at 11:50

2

SQL Server requires that you repeat the expression in the HAVING clause (or use a CTE or subquery). You have no aggregation functions, so I presume you intend:

select round(sum(TaxableAmount), 0) as t,
       round(sum(TaxValue*100/TaxPercentage), 0) as t2
from VoucherTaxDetail
where TaxPercentage <> 0 and TaxableAmount > 0 and
      TableName in ('TVPayment', 'Payment')
group by transactionId
having round(sum(TaxableAmount), 0) <> round(sum(TaxValue*100/TaxPercentage), 0);

answered Sep 17 '18 at 11:50

Gordon Linoff

1,242,037
58
646
786

Thats great!! its working thanks for answer but it is doing same thing as it i have done in my answers query. Check out what i have found below. I have done it using WITH clause. – Ajay Kumar Sep 18 '18 at 05:29

jarlh · Answer 2 · 2018-09-17T12:06:04.687

1

You can't use column aliases in the HAVING clause. The trick is to wrap the query up as a derived table (sub-query). Then you can have those aliases in the outer WHERE clause.

select *
from
(
    select round(TaxableAmount,0) as t, round(((TaxValue*100)/TaxPercentage),0)as t2
    from VoucherTaxDetail 
    where  TaxPercentage<>0 and TaxableAmount>0 and TableName in('TVPayment','Payment')
    group by transactionId
) dt
where t<>t2

edited Sep 17 '18 at 12:06

answered Sep 17 '18 at 11:49

jarlh

42,561
8
45
63

@SalmanA, you mean the GROUP BY comment? Well, at least this shows how to use column aliases in a where clause. – jarlh Sep 17 '18 at 12:07
The above is not working and giving the below error. Column 'VoucherTaxDetail.TaxableAmount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Ajay Kumar Sep 18 '18 at 05:27

Ajay Kumar · Answer 3 · 2018-09-19T07:02:50.130

0

However, i found this answer for my query. I just thought of using the output of the inner query with WITH clause and CTE. Basically, what i have understood that we cannot use the output of alias in HAVING clause for applying conditions.

 with cte as (
 Select round(TaxableAmount,0) as t,
 round(((TaxValue*100)/TaxPercentage),0)as t2 
 from VoucherTaxDetail where
 TaxPercentage<>0 and TaxableAmount>0 and TableName in('TVPayment','Payment'))
 select * from cte where t<>t2

edited Sep 19 '18 at 07:02

answered Sep 18 '18 at 05:29

Ajay Kumar

11
4

A good answer has a better explanation [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer) – Narendra Jadhav Sep 18 '18 at 05:50
While this might answer the authors question, it lacks some explaining words and links to documentation. Raw code snippets are not very helpful without some phrases around it. You may also find [how to write a good answer](https://stackoverflow.com/help/how-to-answer) very helpful. Please edit your answer. – hellow Sep 18 '18 at 07:52

SQL Server query having clause

3 Answers3