locked
Don't SUM Specific Groups RRS feed

  • Question

  • Hi All,

    Running SQL Server 2012.  DDL and Sample Data:

    CREATE TABLE [dbo].[Test]
        (
        [ID] [int],
        [Category] [varchar](50),
        [Quantity] [int]
        )
    GO
    
    INSERT INTO [dbo].[Test]
        ([ID], [Category], [Quantity])
    VALUES
        (1, 'A', 1),
        (2, 'A', 1),
        (3, 'A', 1),
        (4, 'B', 16),
        (5, 'B', 16),
        (6, 'C', 1),
        (7, 'C', 1),
        (8, 'C', 1),
        (9, 'C', 1),
        (10, 'C', 1)
    GO

    We have a requirement to sum by the category, which is simple to do and by using the query below:

    SELECT DISTINCT
        [Category],
        SUM([Quantity]) AS [Quantity]
    FROM
        [dbo].[Test]
    GROUP BY
        [Category]

    I get the following results:

    Category | Quantity
    A	 | 3
    B	 | 32
    C	 | 5
    However, in this case we'd like the SUM to not be used on category B.  How would we structure the query to return the following data:

    Category | Quantity
    A	 | 3
    B	 | 16
    C	 | 5

    We have no control over the data we're reporting on, so a redesign (although preferred) isn't an option.

    Any help is greatly appreciated!

    Best Regards

    Brad

    Saturday, April 16, 2016 6:47 PM

Answers

  • Since you don't want to aggregate the sum from category B, you can remove it with a WHERE clause. However, you want to include it based on some rules, so you might want to use a UNION to include it back into the result set.

    How about this:

    SELECT DISTINCT
        [Category],
        SUM([Quantity]) AS [Quantity]
    FROM
        [dbo].[Test]
    WHERE Category != 'B'
    GROUP BY
        [Category]
    UNION
    SELECT  [Category],
    		[Quantity]
    FROM
        [dbo].[Test]
    WHERE Category = 'B'
    ORDER BY Category

    Resulting in:

    Category Quantity
    A 3
    B 16
    C 5

    Thanks,
    Sam Lester (MSFT)


    http://blogs.msdn.com/b/samlester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Saturday, April 16, 2016 7:29 PM
  • What do you want if the Quantity values for 'B' differ?  If you know they are always the same or if you want the largest value, you can do

    SELECT
        [Category],
        CASE WHEN Category = 'B' THEN MAX([Quantity]) ELSE SUM([Quantity]) END AS [Quantity]
    FROM
        [dbo].[Test]
    GROUP BY
        [Category]

    If you want the smallest value or average value just replace MAX with the appropriate function in the above.

    Note that you do not need DISTINCT.  Since you have a GROUP BY, you cannot possibly have duplicate rows in the result.

    Tom

    Saturday, April 16, 2016 10:51 PM

All replies

  • Since you don't want to aggregate the sum from category B, you can remove it with a WHERE clause. However, you want to include it based on some rules, so you might want to use a UNION to include it back into the result set.

    How about this:

    SELECT DISTINCT
        [Category],
        SUM([Quantity]) AS [Quantity]
    FROM
        [dbo].[Test]
    WHERE Category != 'B'
    GROUP BY
        [Category]
    UNION
    SELECT  [Category],
    		[Quantity]
    FROM
        [dbo].[Test]
    WHERE Category = 'B'
    ORDER BY Category

    Resulting in:

    Category Quantity
    A 3
    B 16
    C 5

    Thanks,
    Sam Lester (MSFT)


    http://blogs.msdn.com/b/samlester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Saturday, April 16, 2016 7:29 PM
  • What do you want if the Quantity values for 'B' differ?  If you know they are always the same or if you want the largest value, you can do

    SELECT
        [Category],
        CASE WHEN Category = 'B' THEN MAX([Quantity]) ELSE SUM([Quantity]) END AS [Quantity]
    FROM
        [dbo].[Test]
    GROUP BY
        [Category]

    If you want the smallest value or average value just replace MAX with the appropriate function in the above.

    Note that you do not need DISTINCT.  Since you have a GROUP BY, you cannot possibly have duplicate rows in the result.

    Tom

    Saturday, April 16, 2016 10:51 PM
  • So is it always B you dont want to aggregate? Or is there a rule to determine that. Also can Quantity values vary for a category? in that case, what value should you return?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, April 17, 2016 5:11 AM