Nested select statements / running total
-
I am in need of some assistance. I have two select statements that work fine by them selves but when I try to nest them, I get an error message about the last line. I guess SQL doesn’t like the two as statements right next to each other. If I store the nested select statement as a user defined function, it works.
Next problem is when I run the main select statement to get the running total, but I cannot access the data. It returns as an alias table and when I try to reference that alias I get an “it does not exist” error. Any help would be very helpful. I have done some coding and data base work before but am new to T-SQL
SELECT ordbot.[time]
,ordbot.density
, (SELECT Sum([fn_botsort].[avg_thick])
AS Total
-- derived table to select blocks I want and put in order
FROM (select distinct top 100 percent [time]
, block_id
, density
, block_side
, avg_thick
from thickness_table
where (block_id = [conditional parameter] )and (block_side like 'bottom')
order by [time] )
as ordbot
WHERE ((([fn_botsort].[time])<=[tbl_Alias].[time])))
AS Total
-- derived table to select blocks I want and put in order same as above
FROM (select distinct top 100 percent [time]
, block_id
, density
, block_side
, avg_thick
from thickness_table
where (block_id = [conditional parameter])and (block_side like 'bottom')
order by [time] )
as ordbot
AS [tbl_Alias]
-
Your query doesn't look right at all. Can you post you table structure and explain you goal, looks like you want
Time,
Density,
and Sum([fn_botsort].[avg_thick])
But there is no alias fn_botsort which I think maybe you wanted the first subquery to be fn_botsort. Is avg_thick supposed to be a cumulative for each time and density or is it supposed to be fore each time and density you want the total avg_thick. More detail will help us help you better.
-
Just guessing:
SELECT ordbot.[time]
,ordbot.density
, (SELECT Sum([fn_botsort].[avg_thick])
AS Total
-- derived table to select blocks I want and put in order
FROM (select distinct top 100 percent [time]
, block_id
, density
, block_side
, avg_thick
from thickness_table
where (block_id = [conditional parameter] )and (block_side like 'bottom')
order by [time] )
as [fn_botsort]
WHERE ((([fn_botsort].[time])<=[ordbot].[time])))
AS Total
-- derived table to select blocks I want and put in order same as above
FROM (select distinct top 100 percent [time]
, block_id
, density
, block_side
, avg_thick
from thickness_table
where (block_id = [conditional parameter])and (block_side like 'bottom')
order by [time] )
as ordbot
-
I agree with Antarres. We don't have enough information to give you any meaningful help. The syntx of the query above is completely out of whack.
After looking at this for a while here is what I would re-write it as.
SELECT ordbot.[time]
,ordbot.density
, Total = (SELECT Total =Sum(ordbot.avg_thick)
FROM (SELECT DISTINCT TOP 100 PERCENT
[time]
, avg_thick
FROM thickness_table
WHERE block_id = [conditional parameter]
AND block_side LIKE 'bottom'
ORDER BY [time]
) AS ordbot
WHERE ordbot.[time] <= tbl_Alias.[time]
)
FROM (SELECT DISTINCT TOP 100 PERCENT
[time]
, density
, avg_thick
FROM thickness_table
WHERE block_id = [conditional parameter]
AND block_side LIKE 'bottom'
ORDER BY [time]
) AS tbl_Alias
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
I am too confused too by the post. anyway the simple method to get a running total is to sum the measute within a subquery , for all ranges below the dimension and equate the constraints to break by, while ordering them on the query.
example , to find freight charges by customer, orderid in northwind database, this query would give you the running totals
select customerid,orderid, orderdate, freight ,(select sum(freight) from orders y where y.orderid <= x.orderid and y.customerid = x.customerid) as running_total
from orders x
order by customerid, orderid
thanks
vishy
-
One of the reason the query didn’t look right is it wasn’t. I tried many different things and didn’t clean it up right sorry. I am not using a function as a reference instead I am trying to use this nested query. There is most likely a better way to do this.
This is what I want to do many sheets are cut from a block. I need to find out where on the block they were cut from. The blocks can be cut from the top or bottom.
My thought was to divide the block by top and bottom do a running total of thickness of sheets based on time and then union them together.
I have now included the table I am working with. It is very messy, but that is another battle for another day and the only thing I can say is I did not design it.
SELECT ordbot.[time]
,ordbot.density
, (SELECT Sum([ordbot].[avg_thick])
AS Total
-- derived table to select blocks I want and put in order
FROM (select distinct top 100 percent [time]
, block_id
, density
, block_side
, avg_thick
from thickness_table
where (block_id = [conditional parameter] )and (block_side like 'bottom')
order by [time] )
as ordbot
WHERE ((([ordbot].[time])<=[tbl_Alias].[time])))
AS Total
-- derived table to select blocks I want and put in order same as above
FROM (select distinct top 100 percent [time]
, block_id
, density
, block_side
, avg_thick
from thickness_table
where (block_id = [conditional parameter])and (block_side like 'bottom')
order by [time] )
as ordbot
AS [tbl_Alias]
1 autonumber int 4 0
0 [DATE] datetime 8 1
0 [TIME] datetime 8 0
0 TRAVELER int 4 1
0 BLOCK_ID nvarchar 50 1
0 SHEET int 4 1
0 SHEET_ID int 4 1
0 THIC_REQ float 8 1
0 WEIGHT float 8 1
0 WIDTH real 4 1
0 LENGTH real 4 1
0 THICK1 float 8 1
0 THICK2 float 8 1
0 THICK3 float 8 1
0 THICK4 float 8 1
0 THICK5 float 8 1
0 THICK6 float 8 1
0 THICK7 float 8 1
0 THICK8 float 8 1
0 THICK9 float 8 1
0 USL_DENSITY float 8 1
0 LSL_DENSITY float 8 1
0 UL_THICK float 8 1
0 LL_THICK float 8 1
0 DENSITY float 8 1
0 TAR_DEN int 4 1
0 PRODUCT nvarchar 50 1
0 SHEET_CH float 8 1
0 DTHICK1 float 8 1
0 DTHICK2 float 8 1
0 DTHICK3 float 8 1
0 DTHICK4 float 8 1
0 DTHICK5 float 8 1
0 DTHICK6 float 8 1
0 DTHICK7 float 8 1
0 DTHICK8 float 8 1
0 DTHICK9 float 8 1
0 DDENSITY float 8 1
0 BLOCK_SIDE nvarchar 50 1
0 SAVG_TH float 8 1
0 STATUS nvarchar 50 1
0 SAW_ID nvarchar 53 1
0 DEN_RND float 8 1
0 AVG_THICK real 4 1
0 [TIMESTAMP] binary 8 1
-
Ok thanks to vishy and all who responded new code works.
this is what I have are there ways to make it better
select BLOCK_ID
,DENSITY
,AVG_THICK
,(select sum(AVG_THICK)
from dbo.THICKNESS_TABLE y
where y.[TIME] <= x.[TIME]
and y.BLOCK_ID = x.BLOCK_ID
and BLOCK_SIDE like 'bottom')
as running_total
from dbo.THICKNESS_TABLE x
where BLOCK_ID like [conditional parameter] and BLOCK_SIDE like 'bottom'
union
select BLOCK_ID
,DENSITY
,AVG_THICK
,(select 23-sum(AVG_THICK)
from dbo.THICKNESS_TABLE y
where y.[TIME] <= x.[TIME]
and y.BLOCK_ID = x.BLOCK_ID
and BLOCK_SIDE like 'top')
as running_total
from dbo.THICKNESS_TABLE x
where BLOCK_ID like [conditional parameter] and BLOCK_SIDE like 'top'
order by running_total
-
I am not quite sure how the data looks like but here is a wild guess
select
BLOCK_ID
, DENSITY
, AVG_THICK
, BLOCK_SIDE
, (case when BLOCK_SIDE ='top' then 23 else 0 end)
- (case when BLOCK_SIDE ='top' then 1 else -1 end) *
(select sum(AVG_THICK)
from dbo.THICKNESS_TABLE y
where y.[TIME] <= x.[TIME]
and y.BLOCK_ID = x.BLOCK_ID)
as running_total
from dbo.THICKNESS_TABLE x
where BLOCK_ID like [conditional parameter]
group by BLOCK_ID, DENSITY, AVG_THICK, BLOCK_SIDE
I assumed BLOCK_SIDE can only be 'top' or 'bottom', right?
hth
* Noel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply