SQL Server Performance

Top 10 rows and rest as Others

Discussion in 'SQL Server 2005 General Developer Questions' started by parveen, Oct 23, 2009.

  1. parveen New Member

    Hi friends,
    My sample data is as follows


    DECLARE @Sample TABLE
    (
    fbNumINT,
    Customervarchar(50),
    paidAmountFLOAT,
    WeightINT

    )

    INSERT INTO @Sample
    SELECT 39161976,'AMC-CARTHAGE',50.00,2 UNION
    SELECT 39161988,'AMC-CARTHAGE',60.00,3 UNION
    SELECT 39161985,'AMC-CARTHAGE',50.00,4 UNION
    SELECT 38451329,'CITRY HEALTHARE',11646.00,39915 UNION
    SELECT 39195692,'WESCO AIRCRAFT',9668.00,1 UNION
    SELECT 39195691,'WESCO AIRCRAFT',9187.00,1 UNION
    SELECT 39181135,'FREUDENBERG NOL',7948.00,20435 UNION
    SELECT 39181136,'HOME', 20164.03,64867UNION
    SELECT 39195700,'BARDONS AND OLI',4204.34,20000 UNION
    SELECT 38714013,'VALASSIS',4196.33,3270 UNION
    SELECT 39198077,'PAGELL',3178.19,17134 UNION
    SELECT 39205936,'DELPHI',2000.00,50 UNION
    SELECT 39205942,'DELPHI',3000.00,50 UNION
    SELECT 39199519,'UNITED SOLAR SY',2550.00,3660

    Here,I want a result like,Top 10 customers in grouping of a substring of thefirst five characters of the customer name and rest all as 'Others'with sum of weight and paid amount other than first 10.

    So Expected output is something like this ..

    'AMC-CARTHAGE',110.00,9
    'CITRY HEALTHARE',11646.00,39915
    'WESCO AIRCRAFT',18855.00,2
    'FREUDENBERG NOL',7948.00,20435
    'HOME', 20164.03,64867
    'BARDONS AND OLI',4204.34,20000
    'VALASSIS',4196.33,3270
    'PAGELL',3178.19,17134
    'DELPHI',5000.00,100
    'UNITED SOLAR SY',2550.00,3660
    'Others',1215487454.00,551515151

    Thanks.

  2. FrankKalis Moderator

    There are a couple of things I don't understand in your requirement, but this should give you some ideas:
    ;WITH cte
    AS(SELECT
    ROW_NUMBER() OVER (ORDER BY S.Customer) AS rn, S.Customer,
    SUM(S.paidAmount) AS PaidAmount,
    SUM(S.Weight) AS Weight
    FROM
    @Sample S
    GROUP BY
    S.Customer)

    SELECT
    cte.Customer,
    cte.PaidAmount,
    cte.Weight
    FROM
    cte
    WHERE
    rn < = 10
    UNION ALL
    SELECT
    'others',
    SUM(cte.PaidAmount),
    SUM(cte.Weight)
    FROM
    cte
    WHERE
    rn > 10
    GROUP BY
    cte.Customer

  3. parveen New Member

    Hi Frank thanks for your reply,
    I shall be more clear with data and requirement.
    sample data as follows - ( I have added few rows to previous data thats all )
    DECLARE @Sample TABLE
    (
    fbNum INT,
    Customer varchar(50),
    paidAmount FLOAT,
    Weight INT

    )

    INSERT INTO @Sample
    SELECT 39161976, 'AMC-CARTHAGE', 5000.00, 2 UNION
    SELECT 39161988, 'AMC-CARTHAGE', 6000.00, 3 UNION
    SELECT 39161985, 'AMC-CARTHAGE', 5000.00, 4 UNION
    SELECT 38451329, 'CITRY HEALTHARE', 11646.00, 39915 UNION
    SELECT 39195692, 'WESCO AIRCRAFT', 9668.00, 1 UNION
    SELECT 39195691, 'WESCO AIRCRAFT', 9187.00, 1 UNION
    SELECT 39181135, 'FREUDENBERG NOL', 7948.00, 20435 UNION
    SELECT 39181136, 'HOME', 20164.03, 64867 UNION
    SELECT 39195700, 'BARDONS AND OLI', 4204.34 ,20000 UNION
    SELECT 38714013, 'VALASSIS', 4196.33, 3270 UNION
    SELECT 39198077, 'PAGELL', 3178.19, 17134 UNION
    SELECT 39205936, 'DELPHI', 2000.00, 50 UNION
    SELECT 39205942, 'DELPHI', 3000.00, 50 UNION
    SELECT 39199519, 'UNITED SOLAR SY', 2550.00, 3660 UNION
    SELECT 39036260, 'BIG D SUPPLIES',3778.93, 38714 UNION
    SELECT 39158839, 'FAURECIA INTERI', 3700.00, 1 UNION
    SELECT 38849481, 'THYSSENKRUPP AE',3630.19, 5500
    Here i need an output with grouping of substring of first Five characters ( i.e say delphi has 2 rows so that need to be counted as single entry n summed up) and ordering is by sum of paid amount in big to small order.And Others is sum of rest customers.
    So output needed is like -
    'HOME',20164.03 64867
    'WESCO AIRCRAFT',18855, 2
    'AMC-CARTHAGE',16000, 9
    'CITRY HEALTHARE',11646 ,39915
    'FREUDENBERG NOL',7948, 20435
    'DELPHI',5000, 100
    'BARDONS AND OLI', 4204.34, 20000
    'VALASSIS', 4196.33, 3270
    'BIG D SUPPLIES', 3778.93, 38714
    'FAURECIA INTERI', 3700, 1
    'Others' , 9358.38,26294
    Thanks & regards
    Parveen.

  4. FrankKalis Moderator

    What about?
    ;WITH cte
    AS(SELECT
    ROW_NUMBER() OVER (ORDER BY SUM(paidAmount) DESC) AS rn, S.Customer,
    SUM(S.paidAmount) AS PaidAmount,
    SUM(S.Weight) AS Weight
    FROM
    @Sample S
    GROUP BY
    S.Customer)

    SELECT
    cte.Customer,
    cte.PaidAmount,
    cte.Weight
    FROM
    cte
    WHERE
    rn < = 10
    UNION ALL
    SELECT
    'others',
    SUM(cte.PaidAmount),
    SUM(cte.Weight)
    FROM
    cte
    WHERE
    rn > 10

  5. parveen New Member

    Yes Frank Thanks a lot, That works fine but just need to add that substring part.cause there is inconsistency in data,so i need to add up all that first 5 characters match for customer.
    Thanks a ton !!
    Regards
    Parveen.
  6. parveen New Member

    Hi,
    I think that can be done with this ,what say ?
    ;WITH cte
    AS(SELECT
    ROW_NUMBER() OVER (ORDER BY SUM(paidAmount) DESC) AS rn, MAX(S.Customer) AS Customer,
    SUM(S.paidAmount) AS PaidAmount,
    SUM(S.Weight) AS Weight
    FROM
    @Sample S
    GROUP BY
    substring( S.Customer,1,5))

    SELECT
    cte.Customer,
    cte.PaidAmount,
    cte.Weight
    FROM
    cte
    WHERE
    rn < = 10
    UNION ALL
    SELECT
    'others',
    SUM(cte.PaidAmount),
    SUM(cte.Weight)
    FROM
    cte
    WHERE
    rn > 10
    Thanks
    Parveen
  7. FrankKalis Moderator

    If it works, it's okay. But, if possible, I would try to fix these data inconsistencies. [:)]
  8. parveen New Member

    Yeah i agree,but the reason is its our maintainence and enhancement project and database size is 18 gb.
    Once again thanks.
    Regards
    Parveen.

Share This Page