Adding Computed Column

Question

Adding Computed Column

bpowers

SSCertifiable

Points: 5483
More actions
April 25, 2012 at 2:50 pm

#256970

Below is an example of a script I have, which works fine. However, I would now like to multiply the "AVERAGE_QTY" by the "USED". Any idea how I can accomplish this? Any help will be appreciated.
SELECT P.ID
,P.NAME
,SUM(AQTY+BQTY)/COUNT (P.LINES) AS AVERAGE_QTY
,USED
FROM PART P
LEFT JOIN (--USED----------
SELECT PH.ID
,PH.NAME
,SUM(PH.USED) AS USED
FROM PART HEADER PH
WHERE PH.STATUS = 'O'
)USED on P.ID = USED.ID
WHERE P.LINE_TYPE = 'R'
GROUP BY P.ID, P.NAME, USED

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

bpowers (4/25/2012)
Below is an example of a script I have, which works fine. However, I would now like to multiply the "AVERAGE_QTY" by the "USED". Any idea how I can accomplish this? Any help will be appreciated.
SELECT P.ID
,P.NAME
,SUM(AQTY+BQTY)/COUNT (P.LINES) AS AVERAGE_QTY
,USED
FROM PART P
LEFT JOIN (--USED----------
SELECT PH.ID
,PH.NAME
,SUM(PH.USED) AS USED
FROM PART HEADER PH
WHERE PH.STATUS = 'O'
)USED on P.ID = USED.ID
WHERE P.LINE_TYPE = 'R'
GROUP BY P.ID, P.NAME, USED

Are you sure this works? Your sub-query does not have a group by.

Jared
CE - Microsoft

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

bpowers (4/25/2012)
Below is an example of a script I have, which works fine. However, I would now like to multiply the "AVERAGE_QTY" by the "USED". Any idea how I can accomplish this? Any help will be appreciated.
SELECT P.ID
,P.NAME
,SUM(AQTY+BQTY)/COUNT (P.LINES) AS AVERAGE_QTY
,USED
FROM PART P
LEFT JOIN (--USED----------
SELECT PH.ID
,PH.NAME
,SUM(PH.USED) AS USED
FROM PART HEADER PH
WHERE PH.STATUS = 'O'
)USED on P.ID = USED.ID
WHERE P.LINE_TYPE = 'R'
GROUP BY P.ID, P.NAME, USED

Pretty much the same way.

(SUM(AQTY+BQTY)/COUNT (P.LINES)) * P.USED

You can't reference the computed column by name.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

bpowers SSCertifiable Points: 5483 More actions · Answer 3

It has a few unions and a group by. It was close to quiting time, so I got lazy in my example. 🙂

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

So you got it figured out now??

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

bpowers SSCertifiable Points: 5483 More actions · Answer 5

Not yet, but I am working on it. It didn't like the P.USED.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

bpowers (4/26/2012)
Not yet, but I am working on it. It didn't like the P.USED.

Not sure what you mean by "It didn't like". 😛

I should be the same as you did in your select list.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

drew.allen SSC Guru Points: 76985 More actions · Answer 7

Sean Lange (4/26/2012)
bpowers (4/26/2012)
Not yet, but I am working on it. It didn't like the P.USED.
Not sure what you mean by "It didn't like". 😛
I should be the same as you did in your select list.

No the one in the original select list didn't specify a table name/alias, but it was referring to USED.USED. That's part of the reason that I always try to use two-part naming for my columns.

If you change it to USED.USED, it should work.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

drew.allen (4/26/2012)
Sean Lange (4/26/2012)
bpowers (4/26/2012)
Not yet, but I am working on it. It didn't like the P.USED.
Not sure what you mean by "It didn't like". 😛
I should be the same as you did in your select list.
No the one in the original select list didn't specify a table name/alias, but it was referring to USED.USED. That's part of the reason that I always try to use two-part naming for my columns.
If you change it to USED.USED, it should work.
Drew

Yeah I didn't bother to look too closely, figured the OP would spot that one if it was wrong. It would probably have been fine if I had just left the table alias off. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

bpowers SSCertifiable Points: 5483 More actions · Answer 9

Ok, here is what I have:

SELECT p.ID

,p.NAME

,SUM(AQTY+BQTY)/COUNT (P.LINES) AS AVERAGE_QTY

,USED.USED

,(SUM(AQTY+BQTY)/COUNT (P.LINES)) * USED.USED AS TOTAL

FROM PART p

LEFT JOIN (--USED----------

SELECT ph.ID

,ph.NAME

,SUM(ph.SHIPPED) AS USED

FROM PART HEADER ph

WHERE ph.STATUS = 'O'

GROUP BY ph.ID, ph.NAME

)USED on p.ID = USED.ID

WHERE p.LINE_TYPE = 'R'

GROUP BY p.ID, p.NAME, USED.USED

However, I get the following error:

"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

bpowers SSCertifiable Points: 5483 More actions · Answer 10

I got it! Had a parentheses in the wrong spot. Thank you for all your help. It is greatly appreciated!