Query in Having Clause

Question

Query in Having Clause

avdhut.k

SSCrazy

Points: 2834
More actions
August 31, 2012 at 3:24 am

#280915

Hi,
Select Lnno From Temp_Check
Where det_trantype = 'Opbal'
Group By Lnno
Having Sum(Clbal) <> 0
This query return the output where Sum(Clbal) <> 0,
but In this table ,one more culumn is there trantype .
Now I want same output but except trantype = 'Obal'
That means,even though clbal = 0 for trantype = 'Obal' it should not ignore it.
Please help me !

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

You will need to set up some sample data for this, there's a link in my sig to a great article which shows you how it's done.

Without knowing how values of column [trantype] change relative to column [Lnno], it's not possible to give only one query as an answer.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 2

I don't fully understand what you mean, but I think you want something like this, more info would be good (a stab in the dark!)

SELECT Lnno FROM Temp_Check

WHERE det_trantype = 'Opbal'

GROUP BY Lnno

HAVING SUM(Clbal) <> 0

UNION ALL

SELECT Lnno FROM Temp_Check

WHERE det_trantype <> 'Opbal'

GROUP BY Lnno

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

rhythmk SSCertifiable Points: 7271 More actions · Answer 3

avdhut.k (8/31/2012)
Hi,
Select Lnno From Temp_Check
Where det_trantype = 'Opbal'
Group By Lnno
Having Sum(Clbal) <> 0
This query return the output where Sum(Clbal) <> 0,
but In this table ,one more culumn is there trantype .
Now I want same output but except trantype = 'Obal'
That means,even though clbal = 0 for trantype = 'Obal' it should not ignore it.
Please help me !

To help you please help us to get the table DDL and sample data and the required output.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂