calculated field based on alias

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

calculated field based on alias

Author

Topic

seanh1016
Starting Member

14 Posts

Posted - 2010-09-11 : 14:44:45

I'm basically trying to allocate 'code' factors in one table to the sums in another table by the entry in 'column2'. So, is something like below possible when 'code1,2,3,4,5' are column names from a different table than Amount that are joined correctly?

also if column1 and column2 are in GROUP BY below, does this also have to be in GROUP BY?
code1*[sum amt],
code2*[sum amt],
code3*[sum amt],
code4*[sum amt],
code5*[sum amt],
code6*[sum amt]

SELECT
column1,
column2,
sum (amount) as 'sum amt'
code1*[sum amt],
code2*[sum amt],
code3*[sum amt],
code4*[sum amt],
code5*[sum amt],
code6*[sum amt]

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-12 : 03:09:11

columns returning single result (as sum, count, multiply,etc.) do not need to be grouped by if your select is written correctly (e.g.: when using joins on table be aware which fields you are binding and which you are grouping and suming).

if you can give a better look into your query it would be great.

seanh1016
Starting Member

14 Posts

Posted - 2010-09-12 : 14:00:31

I guess my real question is:

is multiplying by the result of an aggregate function the same as just multiplying by the column before it became aggregated? so, code1*[sum amount] or code1*amount?

if not, how do I multiply by the summed 'amount'?

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-12 : 14:15:07

sure is not the same. if you just apply a simple everyday life to your scenario you will find answer to your question. or if you apply a simple matematic "rule of associativity": (a+b)*c = a*c + a*b will fail in your case, becaue you would do either a*c or a*b.

send your query so we can do this the right way.

seanh1016
Starting Member

14 Posts

Posted - 2010-09-12 : 15:32:26

I want C to be multiplied to A or B, whichever it corresponds to. So, I want C to be the summed amount. I'm not looking to do something like SUMPRODUCT in excel, which would give me the wrong answer.

I'm wondering too if I need to include any of these code columns in the OVER(Partition BY...) statement:

SELECT
bc.person_id,
person,
bd.company AS co, (CONVERT(char(5), bd.company) + '-' + CONVERT(char(6), bd.city) + '-' + CONVERT(char(2),

bd.client)) AS Acct,
sum (bd.amount) as 'sum amt'
adj_code,
code1*[sum amt],
code2*[sum amt],
code3*[sum amt],
code4*[sum amt],
code5*[sum amt],
code6*[sum amt]

FROM
(
select
bc.person_id,
person,
bd.company AS co,
(CONVERT(char(5), bd.company) + '-' + CONVERT(char(6), bd.city) + '-' + CONVERT(char(2), bd.client)) AS Acct,
bd.amount,
ROW_NUMBER() OVER(PARTITION BY bc.person_id, person, bd.company, (CONVERT(char(5),bd.company) + '-' + CONVERT(char

(6), bd.city) + '-' + CONVERT(char(2), bd.client)), bd.amount ORDER BY person) AS RowNum

from
table1 AS bc
INNER JOIN
table2 AS bd
ON bc.company = bd.company
AND bc.BATCH = bd.BATCH
LEFT OUTER JOIN
table3
ON bc.person_id = table3.person_id_ID COLLATE SQL_Latin1_General_CP1_CS_AS
AND bc.company = table3.company
LEFT OUTER JOIN
table4 AS ad
ON bd.ADJRSN = ad.CODE
AND bd.company = ad.company
LEFT OUTER JOIN
table5 AS glc
ON bd.ADJRSN = glc.adj_code
AND bd.company = glc.company

WHERE
(bd.update_date >= CONVERT(DATETIME, '2010-08-22 00:00:00', 102)
and bd.update_date <= CONVERT(DATETIME, '2010-09-07 00:00:00', 102)

)
as t

where
Rownum=1

group by
bc.person_id,
person,
bd.company,
(CONVERT(char(5), bd.company) + '-' + CONVERT(char(6), bd.city) + '-' + CONVERT(char(2), bd.client))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources