December 11, 2021 at 12:35 pm
Hi
In below query i am getting above error
Select T0.Code,T1.Item,
(Select lTotal = sum(LTotal) from P1 T where T.Doc = T1.Doc ) as 'LTotal',
(Select BAmnt = sum(BAmnt) from P1 T where T.Doc = T1.Doc),
(Select Td = sum(Td) from P1 T where T.Doc = T1.Doc),
(ltotal/BAmt)*Td
FROM tb T0
inner join tb1 T1
on T0.Doc = T1.Doc
group by T0.Code,T0.Item
Thanks
December 11, 2021 at 2:52 pm
At a minimum it's missing a comma at the end of the first line. Is it necessary to nest SELECT statements within the SELECT list as you've done? It seems possible you could JOIN or LEFT JOIN to P1 in the outer SELECT
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 11, 2021 at 5:09 pm
You cannot use a column alias here - SQL Server doesn't know anything about those aliases until after the select portion has been processed.
There are further problems with this code - you are performing a correlated subquery for each column and 'naming' the column inside the subquery and expecting that name to be available to the outer query. That won't work either.
The solution for this will be either a CTE or derived table to generate the SUMs - or CROSS APPLY.
Additionally, you need to determine how these values correlate based on each item - else you are not going to get the expected values. You will get the totals by Doc for every Item.
If you would like further help, please provide sample data and expected results (in the form of create/insert statements) and someone here will be able to help.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply