?
Solved

Select statement inside a select statement

Posted on 2006-06-08
5
Medium Priority
?
5,925 Views
Last Modified: 2011-09-20
I'm trying to use this SQL statement:

SELECT     TTAXYR, TRECTN, TMAPN, TMAPA, TPARCL, TPICOD, TSPCIN, TNAME1, TNAME2, TSTADR, TTXAMT, TTXBAL, TGROUP, TTXPAD, TTXPEN, TTPPAD,
                          (SELECT     SUM(TTXBAL)
                            FROM          TAXBILL
                            WHERE      (TTXBAL > 0) AND (TMAPN = TMAPN) AND (TMAPA = TMAPA) AND (TGROUP = TGROUP) AND (TPARCL = TPARCL) AND (TSPCIN = TSPCIN)
                                                   AND (TPICOD = TPICOD) AND (TTAXYR < 2006) AND (TTRSTS = '')) AS TTXDEL
FROM         TAXBILL
WHERE     (TTAXYR = 2006)
ORDER BY TNAME1, TNAME2

But I just get the same value returned, 1430004.5, for every row.  I don't even know if what I'm doign is possible in one sql query.

Thanks
0
Comment
Question by:jcbergman
5 Comments
 

Author Comment

by:jcbergman
ID: 16861644
I guess it may help if you knew why I was tryign to do that.  I have a table with all (past and present) property tax bills.  I'm interested in pulling only this years tax bills, but also adding up any previous balances from previous years and outputting that as one of the columns.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 260 total points
ID: 16861677
you have to use table alias names in the query:

SELECT     TTAXYR, TRECTN, TMAPN, TMAPA, TPARCL, TPICOD, TSPCIN, TNAME1, TNAME2, TSTADR, TTXAMT, TTXBAL, TGROUP, TTXPAD, TTXPEN, TTPPAD,
                          (SELECT     SUM(i.TTXBAL)
                            FROM          TAXBILL i
                            WHERE      (i.TTXBAL > 0) AND (i.TMAPN = t.TMAPN) AND (i.TMAPA = t.TMAPA) AND (t.TGROUP = i.TGROUP) AND (t.TPARCL = i.TPARCL) AND (t.TSPCIN = i.TSPCIN)
                                                   AND (t.TPICOD = i.TPICOD) AND (i.TTAXYR < 2006) AND (i.TTRSTS = '')) AS TTXDEL
FROM         TAXBILL t
WHERE     (TTAXYR = 2006)
ORDER BY TNAME1, TNAME2

0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16861688
--You need to alias you "primary outside" table
SELECT     TTAXYR, TRECTN, TMAPN, TMAPA, TPARCL, TPICOD, TSPCIN, TNAME1, TNAME2, TSTADR, TTXAMT, TTXBAL, TGROUP, TTXPAD, TTXPEN, TTPPAD,
                          (SELECT     SUM(TTXBAL)
                            FROM          TAXBILL
                            WHERE      (TTXBAL > 0) AND (TMAPN = tbill.TMAPN) AND (TMAPA = tbill.TMAPA) AND (TGROUP = tbill.TGROUP) AND (TPARCL = tbill.TPARCL) AND (TSPCIN = tbill.TSPCIN)
                                                   AND (TPICOD = tbill.TPICOD) AND (TTAXYR < 2006) AND (TTRSTS = '')) AS TTXDEL
FROM         TAXBILL tbill
WHERE     (TTAXYR = 2006)
ORDER BY TNAME1, TNAME2
0
 

Author Comment

by:jcbergman
ID: 16861703
Thank you very much!!!
0
 
LVL 11

Expert Comment

by:lluthien
ID: 16861719
you need to give your table an alias,
otherwise you get a mess

then, you need to change some basic stuff..
the subquery isnt supposed to be inside the select clause.

basically, you need this:


select a.*, subquery.total
from tableA a
left join (select sum (myvalue) as total, MyGroupID
             from tableA
             where taxyear < 2006
             group by myGroupID
) subquery on subquery. mygroupID = a.mygroupID
where a.taxyear >2006

you need to define what mygroupID is in your case, of course


0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question