June 5, 2014 at 1:30 am
Here is Table A:
USE [C:\INETPUB\WWWROOT\ITARENA\APP_DATA\ITALCATALOG.MDF]
GO
/****** Object: Table [dbo].[tbl_product] Script Date: 06/05/2014 08:23:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Price] [decimal](18, 2) NULL,
[ImagePath] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OnCatalogPromotion] [bit] NULL,
[OnServicePromotion] [bit] NULL,
[ImagePathBig] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InStock] [bit] NOT NULL CONSTRAINT [DF_tbl_product_InStock] DEFAULT ((1)),
[TechDesc] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MoreImagePath1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MoreImagePath2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MoreImagePath3] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tbl_product] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
_______________________________________________________
Here is Table B:
USE [C:\INETPUB\WWWROOT\ITARENA\APP_DATA\ITALCATALOG.MDF]
GO
/****** Object: Table [dbo].[tbl_shoppingcart] Script Date: 06/05/2014 08:26:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_shoppingcart](
[CartID] [char](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ProductID] [int] NOT NULL,
[Quantity] [int] NULL,
[DateProductAdded] [datetime] NULL,
[ProductSize] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Color] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShippingCost] [money] NULL,
CONSTRAINT [PK_tbl_shoppingcart] PRIMARY KEY CLUSTERED
(
[CartID] ASC,
[ProductID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_shoppingcart] WITH CHECK ADD CONSTRAINT [FK_tbl_shoppingcart_product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[tbl_product] ([ProductID])
GO
ALTER TABLE [dbo].[tbl_shoppingcart] CHECK CONSTRAINT [FK_tbl_shoppingcart_product]
________________________________________________________
What I NEED:
@GrandTotal = isnull(sum(tableA.Price * TableB.Quantity) + Table.Shippingcost)
That is , I need to get the sum of price multiplied by quantity and the sum of shipping cost ,All summed together.
Many thanks
June 5, 2014 at 1:48 am
I have to ask the obvious:
can't you just join the two tables?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 3:12 am
Many thanks for your concern. Here is what I have done; but is flagging up error:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetTotalAmount]
(@cartid char(36))
AS
BEGIN
declare @Amount money
declare @totAmn money
select isnull(sum(tbl_product.price*tbl_shoppingcart.quantity)+ tbl_shoppingcart.shippingcost,0)
from tbl_shoppingcart Inner Join tbl_product
On tbl_shoppingCart.productid =tbl_product.productid
where tbl_shoppingCart.cartid =@cartid
END
Here is the Error:
Msg 8120, Level 16, State 1, Procedure GetTotalAmount, Line 10
Column 'tbl_shoppingcart.ShippingCost' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
June 5, 2014 at 3:18 am
The error is pretty straight forward:
you need to add a group by clause.
DECLARE @Amount MONEY;
DECLARE @totAmn MONEY;
SELECT ISNULL(SUM(p.price*s.quantity)+ s.shippingcost,0)
FROMtbl_shoppingcarts
INNER JOINtbl_productp ON s.productid =p.productid
WHERE s.cartid =@cartid
GROUP BY s.shippingcost;
Try to format your code. The person who has to read your code will be grateful.
You can also alias your tables, which makes your query more readable.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 3:41 am
Koen Verbeeck (6/5/2014)
The error is pretty straight forward:you need to add a group by clause.
DECLARE @Amount MONEY;
DECLARE @totAmn MONEY;
SELECT ISNULL(SUM(p.price*s.quantity)+ s.shippingcost,0)
FROMtbl_shoppingcarts
INNER JOINtbl_productp ON s.productid =p.productid
WHERE s.cartid =@cartid
GROUP BY s.shippingcost;
Try to format your code. The person who has to read your code will be grateful.
You can also alias your tables, which makes your query more readable.
++++++++++++
Thanks again Koen, I am new here and still trying to grasp this sql stuff. Your suggestions are noted.
I added the Group By and it pops up another error:
Msg 4104, Level 16, State 1, Procedure GetTotalAmount, Line 10
The multi-part identifier "shippingcart.shippingcost" could not be bound.
June 5, 2014 at 3:51 am
nkweke (6/5/2014)
Thanks again Koen, I am new here and still trying to grasp this sql stuff. Your suggestions are noted.
I added the Group By and it pops up another error:
Msg 4104, Level 16, State 1, Procedure GetTotalAmount, Line 10
The multi-part identifier "shippingcart.shippingcost" could not be bound.
Double check your alias. The error means SQL Server cannot find to which table the column shippingcost belongs.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 6:04 am
Koen Verbeeck (6/5/2014)
nkweke (6/5/2014)
Thanks again Koen, I am new here and still trying to grasp this sql stuff. Your suggestions are noted.
I added the Group By and it pops up another error:
Msg 4104, Level 16, State 1, Procedure GetTotalAmount, Line 10
The multi-part identifier "shippingcart.shippingcost" could not be bound.
Double check your alias. The error means SQL Server cannot find to which table the column shippingcost belongs.
You were right I had shippingcart.shippingcost INSTEAD of shoppingcart.shippingcost.
But the formular is giving me a wrong result. where shipping is null or zero. The result turns zero. Again thanks for your time.
June 5, 2014 at 6:18 am
If you add something to NULL, the result is NULL. Then the ISNULL fuction will replace this with 0. So the output is what one would expect considering the formulas you used.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 6:37 am
Koen Verbeeck (6/5/2014)
If you add something to NULL, the result is NULL. Then the ISNULL fuction will replace this with 0. So the output is what one would expect considering the formulas you used.
:w00t: You made my day God bless you, real good. I did set a default value of 0.00 for the shippingcost column. and that was it. Thanks again
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply