Professional Web Applications Themes

SELECT SUM - Microsoft SQL / MS SQL Server

I have table orderProduct: productId quantity price date 1 2 100 18/07/2003 1 3 110 16/07/2003 1 2 90 15/07/2003 2 1 200 17/07/2003 2 2 180 16/07/2003 ..... ..... Now, I have to get sum (quantity*price) for all products for the last date: SELECT sum(quantity*price) FROM orderProduct WHERE date=max(date) fro each product The result should be: 2*100 + 1*200=400 Thank you for your answer, Simon...

  1. #1

    Default SELECT SUM

    I have table orderProduct:

    productId quantity price date
    1 2 100 18/07/2003
    1 3 110 16/07/2003
    1 2 90 15/07/2003
    2 1 200 17/07/2003
    2 2 180 16/07/2003
    .....
    .....

    Now, I have to get sum (quantity*price) for all products for the last date:

    SELECT sum(quantity*price) FROM orderProduct
    WHERE date=max(date) fro each product

    The result should be:

    2*100 + 1*200=400

    Thank you for your answer,
    Simon


    Simon Guest

  2. #2

    Default Re: SELECT SUM

    Try this one. Derived table maxT gives products and its max dates.
    Given that productID and date make a primary key you can join this derived table
    with original and calculate the sum.

    create table test
    (
    productID int not null
    , quantity int
    , price money
    , date datetime not null
    )

    alter table test add constraint PK_test PRIMARY KEY (ProductID, date)

    insert into test
    select 1, 2, 100, convert(datetime, '18/07/2003', 103)
    union all
    select 1 , 3 , 110 , convert(datetime, '16/07/2003', 103)
    union all
    select 1 , 2 , 90 , convert(datetime, '15/07/2003', 103)
    union all
    select 2 , 1 , 200 , convert(datetime, '17/07/2003', 103)
    union all
    select 2 , 2 , 180 ,convert(datetime, '16/07/2003', 103)

    select
    sum(quantity * price)
    from
    (select
    productID, date = max(date)
    from test
    group by productID) maxT
    join test t on maxT.productID = t.productID and maxT.date = t.date


    drop table test


    --
    Dean Savovic
    www.teched.hr


    "Simon" <si> wrote in message news:3f26290d$s5.net... 


    Dean Guest

  3. #3

    Default Re: SELECT SUM

    I think this should give you the result:
    ===============
    -- Some sample scripts
    CREATE TABLE samp_test
    (
    productID INT,
    quantity INT,
    price INT,
    purDate DATETIME
    )
    GO
    INSERT INTO samp_test VALUES (1, 2, 100, '07/18/2003')
    INSERT INTO samp_test VALUES (1, 3, 110, '07/16/2003')
    INSERT INTO samp_test VALUES (1, 2, 90, '07/15/2003')
    INSERT INTO samp_test VALUES (2, 1, 200, '07/17/2003')
    INSERT INTO samp_test VALUES (2, 2, 180, '07/16/2003')
    GO

    -- The answer
    SELECT SUM(quantity*price) FROM
    (
    SELECT productID, MAX(purDate) AS maxDate
    FROM samp_test GROUP BY productID
    ) AS a INNER JOIN samp_test t ON
    a.productID = t.productID AND a.maxDate = t.purDate
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Simon" <si> wrote in message
    news:3f26290d$s5.net... 
    date: 


    SriSamp Guest

  4. #4

    Default Re: SELECT SUM


    SELECT SUM(quantity*price)
    FROM orderproduct AS op1
    WHERE [date] = (
    SELECT MAX([date]) FROM orderproduct AS op2
    WHERE op1.productid = op2.productid
    )

    HTH


    "Simon" <si> wrote in message
    news:3f26290d$s5.net... 
    date: 


    Amy Guest

Similar Threads

  1. Select a list of items into an aliased field when doinga select
    By ehaemmerle in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 18th, 10:49 PM
  2. Replies: 0
    Last Post: September 24th, 03:24 AM
  3. Replies: 0
    Last Post: September 11th, 11:26 AM
  4. Replies: 0
    Last Post: September 11th, 12:19 AM
  5. Replies: 0
    Last Post: April 15th, 01:22 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