locked
sql to display total and grand total in the result RRS feed

  • Question

  • User-721945135 posted

    Hi,

    I have no idea how to add total and grand total like in sample result, and I also want to remove the duplicate equipment name. Do anyone know how? Please guide.

    Below is my query:

    select * from equipment

    And i would like to display my result as below:

    sample result:
    
    Equipment   Type           Qty
    equip_1     A               10
    equip_1     B               10
                       Total:   20
    equip_2     A                8
    equip_2     B               10
                       Total:   18
    equip_3     A               10
    equip_3     B               10
                       Total:   20
                     GrandTotal:58
    
    Thursday, September 27, 2012 11:38 AM

All replies

  • User1264447444 posted
    select Equipment,sum(Qty) as Total from equipment group by Equipment
    
    select sum(Qty) as GrandTotal from equipment

    Thursday, September 27, 2012 11:44 AM
  • User269602965 posted
    SELECT
      Equipment,
      Type,
      SUM(Qty) AS Qty
    FROM
      equipment
    GROUP BY ROLLUP
      (Equipment, Type)
    ORDER BY
      (Equipment, Type)
    /
    

    Oracle Advanced Query clause GROUP BY ROLLUP

    Thursday, September 27, 2012 2:50 PM
  • User-1378764701 posted

    Hi,

    Pls use this rollup function.

    SELECT fact_1_id,
           fact_2_id,
           SUM(sales_value) AS sales_value
    FROM   dimension_tab
    GROUP BY ROLLUP (fact_1_id, fact_2_id)
    ORDER BY fact_1_id, fact_2_id;
    
     FACT_1_ID  FACT_2_ID SALES_VALUE
    ---------- ---------- -----------
             1          1     4363.55
             1          2     4794.76
             1          3     4718.25
             1          4     5387.45
             1          5     5027.34
     1 24291.35
             2          1     5652.84
             2          2     4583.02
             2          3     5555.77
             2          4     5936.67
             2          5     4508.74
     2 26237.04
     50528.39
    Sunday, September 30, 2012 9:19 AM
  • User-721945135 posted

    Thanks for reply.

    It's ok if the query is

    select equipment, type, sum(qty)
    from equipment
    group by rollup
    (equipment, type)
    order by 1,2

    but if i add 2 more column, QUERY like below, I will get like RESULT data 

    QUERY: 
    select categories, equipment, type, type2, sum(qty)
    from equipment
    group by rollup
    (categories, equipment, type, type2)
    order by 1,2,3
    
    RESULT:
    Categories Equipment   Type   Type2        Qty
    cat_1	   equip_1     A      CC           10
    cat_1	   equip_1     A		   10
    cat_1	   equip_1     B      DD           10
    cat_1	   equip_1     B                   10
    cat_1      equip_1                         20
    cat_1	   equip_2     A      CC            8
    cat_1	   equip_2     A		    8
    cat_1      equip_2     B      DD           10
    cat_1      equip_2     B                   10
    cat_1      equip_2                         18
    cat_1 				           38
    cat_2	   equip_3     A      CC           10
    cat_2	   equip_3     A                   10
    cat_2	   equip_3     B      DD           10
    cat_2	   equip_3     B                   10
    cat_2      equip_3                         20
    cat_2				           20
                                               58

    but i want my result to be display like this:

    Categories Equipment   Type   Type2        Qty
    cat_1	   equip_1     A      CC           10
    cat_1	   equip_1     B      DD           10
    cat_1      equip_1                         20
    cat_1	   equip_2     A      CC            8
    cat_1      equip_2     B      DD           10
    cat_1      equip_2                         18
    cat_1 				           38
    cat_2	   equip_3     A      CC           10
    cat_2	   equip_3     B      DD           10
    cat_2      equip_3                         20
    cat_2				           20
                                               58

    How to remove the unwanted row?

    Tuesday, October 2, 2012 12:45 AM
  • User-1378764701 posted

    hi, 

    Pls try this Qry..

    select categories, max(equipment), type, max(type2), sum(qty)
    from equipment
    group by rollup
    (categories, type)
    order
    by 1,2

    Regards
    Sunday, October 7, 2012 8:59 AM