Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Adding a column in a query based on a not null column value from a sub

Status
Not open for further replies.

TerriO

Technical User
Jan 9, 2002
84
0
0
US
I am a little "green" and using a very vanilla reporting tool through a web application so I do not have direct access to the database but can query it. I have a column based on a subquery that returns a value, I need to create another column in the result set that counts only if that subquery value is not null.. Below is the example the owned lot will populate a reference number that cannot be counted in the application because its >1 (2278, etc)and the application only allows a SUM calculation, that is why I need the additional "count" column - any suggestions ??

select
substring(r.resourceid,1,6) as Subdivision,
r.resourceid as Lot, r.Address1,
(select top 1 e.EvRefnum from AMDEVENTS e
where
e.resol1id = r.resol1id and
e.resol2id = r.resol2id and
e.resol3id = r.resol3id and
e.restypid = r.restypid and
e.resourceid = r.resourceid and
e.evttype='Owned Lot') as 'Owned Lot Job' ,
1 as [Count]
from AMTRESOURCE r
where
r.restypid='lotid'
and
(select top 1 e.EvRefnum from AMDEVENTS e
where
e.resol1id = r.resol1id and
e.resol2id = r.resol2id and
e.resol3id = r.resol3id and
e.restypid = r.restypid and
e.resourceid = r.resourceid and
e.evttype<>'Owned Lot') is null
and
substring(resourceid,1,3) like '%%sub%%'
order by
resourceid

Terri
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top