Sum Three Columns From Two Tables

  • 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

  • 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

  • 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.

  • 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

  • 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.

  • 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

  • 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.

  • 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

  • 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