use Northwind
Go
select 
dbo.Orders.OrderID
,Cast(dbo.Orders.OrderDate As DATE)Order_Date 
, dbo.Customers.CustomerID 
, dbo.Customers.CompanyName 
, dbo.Products.ProductName
, Cast(dbo.Orders.ShippedDate As DATE) As Ship_Date 
, dbo.[Order Details].UnitPrice 
, dbo.[Order Details].Quantity
, dbo.[Order Details].UnitPrice * Quantity As Sub_Total
, dbo.[Order Details].Discount
, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (dbo.[Order Details].Discount) As Discount_Amount
, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (dbo.[Order Details].Discount)  As total_Amount  -- fix this
, dbo.Orders.Freight 
, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * dbo.[Order Details].Discount  + 
(dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) + dbo.Orders.Freight As Grand_total --fix this

from dbo.Orders 
inner join dbo.Customers on dbo.Customers.CustomerID = dbo.Orders.CustomerID
inner join dbo.[Order Details] on dbo.Orders.OrderID = dbo.[Order Details].OrderID
inner join dbo.Products on dbo.[Order Details].ProductID = dbo.Products.ProductID
where  dbo.orders.ShippedDate is not null and(dbo.orders.ShippedDate >= '1996')
Order by dbo.Orders.ShippedDate Asc

I cannot use the alias field names as part of additional calculations for new columns. 

total_Amount and  Grand_Total cannot be done with my skill level.

One way is to use a cte to get column names that can then be used in the main query.  (If you re not familiar with cte's, google "common table expressions").  So an example would be

;with cte as
(select 
o.OrderID
, Cast(o.OrderDate As DATE) As Order_Date 
, c.CustomerID 
, c.CompanyName 
, p.ProductName
, Cast(o.ShippedDate As DATE) As Ship_Date 
, d.UnitPrice 
, d.Quantity
, d.UnitPrice * d.Quantity As Sub_Total
, d.Discount
, Cast((d.UnitPrice * d.Quantity) * (d.Discount) As money) As Discount_Amount
, o.Freight  
from dbo.Orders as o
inner join dbo.Customers as c on c.CustomerID = o.CustomerID
inner join dbo.[Order Details] as d on o.OrderID = d.OrderID
inner join dbo.Products as p on d.ProductID = p.ProductID
where  o.ShippedDate >= '1996')
select
OrderID
, Order_Date 
, CustomerID 
, CompanyName 
, ProductName
, Ship_Date 
, UnitPrice 
, Quantity
, Sub_Total
, Discount
, Discount_Amount
, Sub_Total - Discount_Amount As total_Amount  -- fix this
, Freight 
, Sub_Total - Discount_Amount + Freight As Grand_total --fix this
from cte
Order by Ship_Date Asc

Note that I also made a couple of other changes. 

First, I gave an alias to each table.  So (for example) I changed inner join dbo.[Order Details] to inner join dbo.[Order Details] as d.  Then everywhere else in the query instead of dbo.[Order Details]., I just need d.  To most people, this makes thee query easier to read and understand.  And it certainly saves a good deal of typing.

Second, I changed where shipped date is not null and shipped date >= '1996' to just shipped date >= '1996'.  You don't need the is not null since if shipped date >= '1996', it will always be true that shipped date is not null.  So the is not null condition doesn't do anything - that is, it can't remove any additional rows or add any additional rows.  So including it can't possibly change the result of the query, but including it makes to query more complex and may cause the optimizer to choose a less efficient query plan and thus, your query may take longer to run.

Tom

Thank you so very much for helping a newbie!

One way is to use a cte to get column names that can then be used in the main query.  (If you re not familiar with cte's, google "common table expressions").  So an example would be

;with cte as
(select 
o.OrderID
, Cast(o.OrderDate As DATE) As Order_Date 
, c.CustomerID 
, c.CompanyName 
, p.ProductName
, Cast(o.ShippedDate As DATE) As Ship_Date 
, d.UnitPrice 
, d.Quantity
, d.UnitPrice * d.Quantity As Sub_Total
, d.Discount
, Cast((d.UnitPrice * d.Quantity) * (d.Discount) As money) As Discount_Amount
, o.Freight  
from dbo.Orders as o
inner join dbo.Customers as c on c.CustomerID = o.CustomerID
inner join dbo.[Order Details] as d on o.OrderID = d.OrderID
inner join dbo.Products as p on d.ProductID = p.ProductID
where  o.ShippedDate >= '1996')
select
OrderID
, Order_Date 
, CustomerID 
, CompanyName 
, ProductName
, Ship_Date 
, UnitPrice 
, Quantity
, Sub_Total
, Discount
, Discount_Amount
, Sub_Total - Discount_Amount As total_Amount  -- fix this
, Freight 
, Sub_Total - Discount_Amount + Freight As Grand_total --fix this
from cte
Order by Ship_Date Asc

Note that I also made a couple of other changes. 

First, I gave an alias to each table.  So (for example) I changed inner join dbo.[Order Details] to inner join dbo.[Order Details] as d.  Then everywhere else in the query instead of dbo.[Order Details]., I just need d.  To most people, this makes thee query easier to read and understand.  And it certainly saves a good deal of typing.

Second, I changed where shipped date is not null and shipped date >= '1996' to just shipped date >= '1996'.  You don't need the is not null since if shipped date >= '1996', it will always be true that shipped date is not null.  So the is not null condition doesn't do anything - that is, it can't remove any additional rows or add any additional rows.  So including it can't possibly change the result of the query, but including it makes to query more complex and may cause the optimizer to choose a less efficient query plan and thus, your query may take longer to run.

Tom

• Proposed as answer by pituachModerator 7 hours 19 minutes ago
• Marked as answer by pituachModerator 4 hours 35 minutes ago

I cannot use the alias field names as part of additional calculations for new columns. 

You might want to read a book on SQL or take a class. IN SQL, a field is part of a temporal value, {YEAR, MONTH DAY, HOUR, MINUTE, SECOND}. I think you meant columns, which are nothing  like a field in your old file systems or the meaning in SQL.

Your mindset is still back in 1960's or earlier technology. This is set-oriented language in which the sets come into existence all at once, as a completed whole. There is no left-to-right processing! Try to get your head around this pseudo_code  

SELECT (a+y) AS x, (b+x) AS y  FROM Foobar;

Before I can define x, I must have y, before I can define y, I must have x! 

Use a CTE to materialize values at a lower level of aggregation and then read a book on Set Theory witha chapter of Russell's paradox.

Thank you. I agree this is "simple thinking" which I what my College taught. Plus the book I am using to learn is sql queries for mere mortals which is quite old.

I will get these new resources. again, thank you.

"Mere Mortals" series was pretty good on the basic coding stuff, but weak on theory. However, any "geek book" that gets to a third edition has to be good. I always tell people not to buy my books until you have 1 to 3 years of DB experience, so you will realize that you are doing something wrong and want to fix it. I tried writing an intro book once, but it was aw

Good day mikemor,

Is Tom Cooper's answer did not give you what you are lookng for?

As much as I understand your question, his response answer it :-)

If something is still open please clarify

Yes it worked and I understand the solution at a basic level. Everyone has been very helpful and I thank you all.

Mike

So please close the thread by marking Tom's answer as the answer to the question :-)

* You are most welcome

* I will close it this time :-)

Next time please remember to close the thread in the end by marking the answer/s

have a great day