Professional Web Applications Themes

SELECT STATEMENT and CALCULATIONS - Microsoft SQL / MS SQL Server

Hi, I would like to know how I do a calculation in SQL if one of the values I am retrieving can equal NULL. See below: --------------------------------------------------------------------- SELECT TotalPrice = SUM(sc.quantity * p.retailPrice) --TotalPrice = SUM(sc.quantity * p.retailPrice * sc.discount) --BUT sc.discount CAN EQUAL NULL!! Therefore how can I work this out?? FROM tblShoppingCarts sc, tblProducts p WHERE sc.productid = p.[id] ----------------------------------------------------------------- The table creation SQL is below: --------------------------------------------------------------------- CREATE TABLE [dbo].[tblProducts] ( [id] [int] NOT NULL , [title] [varchar] (100) NOT NULL , [retailPrice] [money] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[tblShoppingCarts] ( [id] [int] IDENTITY (1, ...

  1. #1

    Default SELECT STATEMENT and CALCULATIONS

    Hi,

    I would like to know how I do a calculation in SQL if one of the values I am
    retrieving can equal NULL.
    See below:

    ---------------------------------------------------------------------

    SELECT
    TotalPrice = SUM(sc.quantity * p.retailPrice)
    --TotalPrice = SUM(sc.quantity * p.retailPrice * sc.discount) --BUT
    sc.discount CAN EQUAL NULL!! Therefore how can I work this out??
    FROM
    tblShoppingCarts sc,
    tblProducts p
    WHERE
    sc.productid = p.[id]


    -----------------------------------------------------------------
    The table creation SQL is below:
    ---------------------------------------------------------------------

    CREATE TABLE [dbo].[tblProducts] (
    [id] [int] NOT NULL ,
    [title] [varchar] (100) NOT NULL ,
    [retailPrice] [money] NOT NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[tblShoppingCarts] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [productid] [int] NOT NULL ,
    [quantity] [int] NOT NULL ,
    [discount] [float] NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[tblProducts] WITH NOCHECK ADD
    CONSTRAINT [PK_tblProducts] PRIMARY KEY CLUSTERED
    (
    [id]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[tblShoppingCarts] WITH NOCHECK ADD
    CONSTRAINT [PK_tblShoppingCarts] PRIMARY KEY CLUSTERED
    (
    [id]
    ) ON [PRIMARY]
    GO


    ---------------------------------------------------------------------
    The insert statements are here:
    NOTE: I am not sure these are correct. Can you take a quick check
    on the int and NULL values (do you have to put quote signs around them?).
    ---------------------------------------------------------------------
    INSERT INTO tblProducts VALUES( 1, 'My Product',100 )
    INSERT INTO tblProducts VALUES( 2, 'His Product',120 )
    INSERT INTO tblProducts VALUES( 3, 'Her Product',150 )
    INSERT INTO tblProducts VALUES( 4, 'Our Product',190 )

    INSERT INTO tblShoppingCarts VALUES( 1, 3, 'NULL' )
    INSERT INTO tblShoppingCarts VALUES( 1, 2, 'NULL' )





    Stephen Guest

  2. #2

    Default Re: SELECT STATEMENT and CALCULATIONS

    Depending on how you want to treat NULL's, I guess you can use the ISNULL
    function. For example, if you want "quantity * retailPrice" only, when
    "discount" is null, you can use: ISNULL(sc.discount, 1).
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Stephen McCormack" <com.au> wrote in message
    news:frrXa.11657$bigpond.net.au... 
    am 


    SriSamp Guest

  3. #3

    Default Re: SELECT STATEMENT and CALCULATIONS

    I really need the entire SUM string:

    SUM(sc.quantity * p.retailPrice * sc.discount)

    Can you use the ISNULL statement in this?
    If so, could you provide an example.

    Thanks heaps for your help!!


    "SriSamp" <co.in> wrote in message
    news:OTr$phx.gbl... [/ref]
    I [/ref]
    them?). 
    >
    >[/ref]


    Stephen Guest

  4. #4

    Default Re: SELECT STATEMENT and CALCULATIONS

    SUM(sc.quantity * p.retailPrice * ISNULL(sc.discount, 1))


    "Stephen McCormack" <com.au> wrote in message news:PUrXa.11720$bigpond.net.au... [/ref]
    > I [/ref]
    > them?). 
    > >
    > >[/ref]
    >
    >[/ref]


    Daniel Guest

  5. #5

    Default Re: SELECT STATEMENT and CALCULATIONS

    Yup. That's right...
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Stephen McCormack" <com.au> wrote in message
    news:i1sXa.11731$bigpond.net.au... [/ref]
    I [/ref]
    them?). 
    >
    >[/ref]


    SriSamp Guest

Similar Threads

  1. SP with Select statement
    By Gerald in forum ASP Database
    Replies: 3
    Last Post: November 27th, 03:38 PM
  2. help with SELECT statement
    By Chris Hohmann in forum ASP
    Replies: 2
    Last Post: August 19th, 05:10 PM
  3. SELECT statement
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 23
    Last Post: August 18th, 01:12 PM
  4. Need help with select statement
    By Don in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 23rd, 04:53 AM
  5. Need some help on a SELECT statement
    By Sydney in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 16th, 01:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139