Help with a calculated column in SQL

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

Help with a calculated column in SQL

Author

Topic

mathomas73
Starting Member

23 Posts

Posted - 2013-06-05 : 12:04:37

Hi everyone

Can you please help me with another query. Not doing bad for my first day at SQL but need some help again.

I've pulled this code together that joins some tables and gives me an output as below. I now want to calculate a TOTAL SALES field by multiply orders * price. Any ideas how I go about a calculated field?

Thanks in advance.

SELECT Colours6.*, ColourPrice.*, ColourOrders.*
FROM Colours6
JOIN ColourPrice
ON colours6.colour = colourprice.colour
JOIN ColourOrders
ON colours6.colour = colourorders.colour

COLOUR SHAPE PRICE ORDER
Red Square 12.99 32
Blue Triangle 8.99 112
Orange Circle 26.99 12
Green Square 15.99 38

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-05 : 12:07:28

SELECT
colour
,shape
,price
,order
,price*order as TotalSales
FROM ....
...
...

Cheers
MIK

mathomas73
Starting Member

23 Posts

Posted - 2013-06-05 : 12:30:11

Can anyone tell me why when I do the following I get the following error - I cant see what I'm doing differently to online help? THX

CREATE TABLE TotalSales
AS (SELECT Colour FROM Colours6)

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-05 : 12:35:17

his is not the syntax for creating a table. perhaps you want to create a "view"?
Create View ViewName
AS
SELECT query goes here

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 13:44:02

if you want to create a table it should be


SELECT Colour, othercolumns.. INTO TotalSales FROM Colours6

and i dont think you'll create a table with just one column called Color and name it TotalSales thats why i guess there should be additional columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mathomas73
Starting Member

23 Posts

Posted - 2013-06-05 : 13:51:46

Sorry MIK, hit a real blocker on this - I'm just not getting the syntax right on this.

SELECT Colours6.*, ColourPrice.*, ColourOrders.*
FROM Colours6
JOIN ColourPrice
ON colours6.colour = colourprice.colour
JOIN ColourOrders
ON colours6.colour = colourorders.colour

This works when I join 3 tables and now I want to multiply price * orders to get total sales, but I just get the syntax wrong. It says Total Sales is unbound?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 13:58:06

for that you need to aggregate orders. at what level you want to get TotalSales?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-05 : 14:03:34

Always try follow the same thread ..so that if there is something I am unable to answer, someone else can do it for you. Anyone who comes in here and see this post will get confused due to your initial requirement and now this last query.. any this is just an advise :)

So this is the original query that gives you the Price and NoOfOrder information? Can you post few rows of ouput of this query?

Cheers
MIK

mathomas73
Starting Member

23 Posts

Posted - 2013-06-05 : 14:16:43

Thanks MIK, its good advice.

if I run this code:

SELECT Colours6.*, ColourPrice.*, ColourOrders.*
FROM Colours6
JOIN ColourPrice
ON colours6.colour = colourprice.colour
JOIN ColourOrders
ON colours6.colour = colourorders.colour

I get this result:

Id Colour Shape Price Colour Orders
1 Red Square Red 12.99 Red 32
2 Blue Triangle Blue 8.99 Blue 112
3 Orange Circle Orange 26.99 Orange 12

mathomas73
Starting Member

23 Posts

Posted - 2013-06-05 : 14:16:47

mathomas73
Starting Member

23 Posts

Posted - 2013-06-05 : 14:20:38

Sorry - I didnt finish.

If I run this code:

SELECT Colours6.*, ColourPrice.*, ColourOrders.*
FROM Colours6
JOIN ColourPrice
ON colours6.colour = colourprice.colour
JOIN ColourOrders
ON colours6.colour = colourorders.colour

I get this result:

Id Colour Shape Price Colour Orders
1 Red Square 12.99 Red 32
2 Blue Triangle 8.99 Blue 112
3 Orange Circle 26.99 Orange 12

This is fine but I now want to calculate price * orders to get total sales.

I'll be honest, I'm not sure if I need to create a table as this is just a view right? I think once I know the syntax I will run with it but in summary I am essentially joining 3 tables, pulling columns into a new view/or table and then looking to calculate a total.

Sorry for sounding dim, I only started this morning and have been tearing my hair out!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 14:24:38

as per this you could simply do Price * Orders to get total sales. not sure what was the issue you faced. My guess is you tried to refer TotalSales alias somewhere directly (may be in ORDER BY). Thats not allowed and will error out. The solution in such case is to either repeat original expression everywhere or make it a derived table and then use alias while selecting from it.
like


SELECT *
FROM
(
SELECT Colours6.*, ColourPrice.*, ColourOrders.*,Price * Orders AS TotalSales
FROM Colours6
JOIN ColourPrice
ON colours6.colour = colourprice.colour
JOIN ColourOrders
ON colours6.colour = colourorders.colour
)t
ORDER BY TotalSales DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-05 : 14:28:33

As MIK_2008 suggested, try the following query and tell us what you get as your output:
[CODE]

SELECT C6.*, CP.*, CO.*, (CP.Price * CO.Orders) AS [TotalSales]
FROM Colours6 C6
JOIN ColourPrice CP
ON C6.colour = CP.colour
JOIN ColourOrders CO
ON C6.colour = CO.colour

[/CODE]

mathomas73
Starting Member

23 Posts

Posted - 2013-06-05 : 15:13:43

Hi all. Thanks for your help its appreciated. I've created the following code, and it executes, but it doesn't create a total sales column?

SELECT Colours6.*, ColourPrice.*, ColourOrders.*
FROM Colours6
JOIN ColourPrice
ON colours6.colour = colourprice.colour
JOIN ColourOrders
ON colours6.colour = colourorders.colour

SELECT Colours6.*, ColourPrice.*, ColourOrders.*, (ColourPrice.Price * ColourOrders.Orders) AS [TotalSales]
FROM Colours6
JOIN ColourPrice
ON Colours6.colour = ColourPrice.colour
JOIN ColourOrders
ON Colours6.colour = ColourOrders.colour

ORDER BY TotalSales DESC

mathomas73
Starting Member

23 Posts

Posted - 2013-06-05 : 15:31:37

Hi all, I was being a bit stupid and with your help I've worked it out. Thank you so much. Day one has ended on a high!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 23:56:48

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources