Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sum Three Columns From Two Tables Expand / Collapse
Author
Message
Posted Thursday, June 5, 2014 1:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:07 AM
Points: 5, Visits: 13
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:[size="6"][/size]

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
Post #1577653
Posted Thursday, June 5, 2014 1:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 13,253, Visits: 11,033
I have to ask the obvious:
can't you just join the two tables?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1577656
Posted Thursday, June 5, 2014 3:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:07 AM
Points: 5, Visits: 13
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.





Post #1577675
Posted Thursday, June 5, 2014 3:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 13,253, Visits: 11,033
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)
FROM tbl_shoppingcart s
INNER JOIN tbl_product p 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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1577678
Posted Thursday, June 5, 2014 3:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:07 AM
Points: 5, Visits: 13
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)
FROM tbl_shoppingcart s
INNER JOIN tbl_product p 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.
Post #1577684
Posted Thursday, June 5, 2014 3:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 13,253, Visits: 11,033
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1577686
Posted Thursday, June 5, 2014 6:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:07 AM
Points: 5, Visits: 13
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.
Post #1577724
Posted Thursday, June 5, 2014 6:18 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 13,253, Visits: 11,033
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1577731
Posted Thursday, June 5, 2014 6:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:07 AM
Points: 5, Visits: 13
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.

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
Post #1577739
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse