T-SQL aggregating

  • Hi all,

    looking at the example data below, is it possible to sum up just those rows where ProductID = 1 and leave these plits for the other rows as they are and have it all displaying in one output?

    select customerID, productID, orderDate, orderAmount from Orders

    customerID productID orderDate orderAmount

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

    1 1 2007-01-01 00:00:00.000 20.00

    1 2 2007-01-02 00:00:00.000 30.00

    1 2 2007-01-05 00:00:00.000 23.00

    1 3 2007-01-04 00:00:00.000 18.00

    2 1 2007-01-03 00:00:00.000 74.00

    2 1 2007-01-06 00:00:00.000 34.00

    2 2 2007-01-08 00:00:00.000 10.00

    many thanks for reading

  • ams00601 (5/27/2016)


    Hi all,

    looking at the example data below, is it possible to sum up just those rows where ProductID = 1 and leave these plits for the other rows as they are and have it all displaying in one output?

    select customerID, productID, orderDate, orderAmount from Orders

    customerID productID orderDate orderAmount

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

    1 1 2007-01-01 00:00:00.000 20.00

    1 2 2007-01-02 00:00:00.000 30.00

    1 2 2007-01-05 00:00:00.000 23.00

    1 3 2007-01-04 00:00:00.000 18.00

    2 1 2007-01-03 00:00:00.000 74.00

    2 1 2007-01-06 00:00:00.000 34.00

    2 2 2007-01-08 00:00:00.000 10.00

    many thanks for reading

    Is this what you are looking for?

    Select customerID, productID, max(orderDate) orderDate, Sum(orderAmount) orderAmount from Orders Where productID=1 group by customerID, productID

    Union all

    select customerID, productID, orderDate, orderAmount from Orders Where productID<>1

    Edit: added group by

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • ams00601 (5/27/2016)


    Hi all,

    looking at the example data below, is it possible to sum up just those rows where ProductID = 1 and leave these plits for the other rows as they are and have it all displaying in one output?

    select customerID, productID, orderDate, orderAmount from Orders

    customerID productID orderDate orderAmount

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

    1 1 2007-01-01 00:00:00.000 20.00

    1 2 2007-01-02 00:00:00.000 30.00

    1 2 2007-01-05 00:00:00.000 23.00

    1 3 2007-01-04 00:00:00.000 18.00

    2 1 2007-01-03 00:00:00.000 74.00

    2 1 2007-01-06 00:00:00.000 34.00

    2 2 2007-01-08 00:00:00.000 10.00

    many thanks for reading

    CREATE TABLE #orders

    (

    customerIDINT

    ,productIDINT

    ,orderDate DATETIME

    ,orderAmount DECIMAL(4,2)

    )

    INSERT INTO #orders VALUES

    (1, 1,'2007-01-01 00:00:00.000', 20.00)

    ,(1, 2 ,'2007-01-02 00:00:00.000', 30.00)

    ,(1, 2,'2007-01-05 00:00:00.000', 23.00)

    ,(1, 3,'2007-01-04 00:00:00.000', 18.00)

    ,(2, 1,'2007-01-03 00:00:00.000', 74.00)

    ,(2, 1,'2007-01-06 00:00:00.000', 34.00)

    ,(2, 2, '2007-01-08 00:00:00.000', 10.00)

    SELECT

    customerID

    ,s.productID

    ,orderDate

    ,orderamount = ISNULL(sp.AggSales,s.orderAmount)

    FROM #orders s

    LEFT JOIN

    (

    SELECT

    AggSales = SUM(orderamount)

    ,productID

    FROM #orders

    WHERE productID = 1

    GROUP BY

    productID

    ) sp ON sp.productID = s.productID

    DROP TABLE #orders

    This is one way of doing it.

    @Roshan, your method won't work as it is because you need to GROUP BY the non-aggregated columns in the first SELECT.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Nice, catch, edited and added group by if at all OP is interested still.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply