none
Need help with a Query RRS feed

  • Question

  • Hello

    I have this query 

     Select right(Rtrim(a_object),3),  sum (bud_req_amt2) as TOTAL1 ,sum (bud_req_amt3) as TOTAL2 from budget where type ='E' 
    Group by a_object

    Here is the result : but what I need is all 130's, 150's,210's,223's total in one line such as 120 = total1 =5094   Total2= 24224

    Friday, August 16, 2019 8:36 PM

All replies

  • You need to have right(Rtrim(a_object),3) in the GROUP BY clause as well.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, August 16, 2019 9:17 PM
  • Hello it didnt work,

    I want to put thsi in a Reporting services but I cant groupit to have a summary by 130's, 120's ...

    Friday, August 16, 2019 10:00 PM
  • it didnt work,

    That's weird...

    Didn't work by changing the GROUP BY clause, as suggested earlier?

    -- code #1
    Select right(Rtrim(a_object), 3),  
           sum (bud_req_amt2) as TOTAL1,
           sum (bud_req_amt3) as TOTAL2 
      from budget 
      where type ='E' 
      group by right(Rtrim(a_object), 3);


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Edited by José Diz Saturday, August 17, 2019 1:01 PM
    Saturday, August 17, 2019 12:11 AM
  • Hi Ysabel1111,

     

    I agree Erland's advice . And you said that 'it didnt work', could you please share us more infoemation ?

     

    In following script, I create a table and insert some simple data and it can work well. If following script could not satisfy your requirement ,could you  please share us your table structure and some sample data along with your expected result? So that we’ll get a right direction and make some test.

     
    IF OBJECT_ID('budget') IS NOT NULL drop table  budget  
    go 
    create table budget 
    (
    a_object varchar(10) ,
    bud_req_amt2 numeric(10,2),
    bud_req_amt3 numeric(10,2),
    type varchar(10)
    )
    insert into budget values 
    ('123562',2.3,23.55,'E'),
    ('130562',2.3,23.55,'E'),
    ('365555',2.3,23.55,'E'),
    ('789555',2.3,23.55,'E'),
    ('175649',2.3,23.55,'E')
    
    
    Select right(Rtrim(a_object),3) a_object,  
    sum (bud_req_amt2) as TOTAL1 ,
    sum (bud_req_amt3) as TOTAL2 
    from budget where type ='E' 
    Group by a_object
    /*
    a_object TOTAL1                                  TOTAL2
    -------- --------------------------------------- ---------------------------------------
    562      2.30                                    23.55
    562      2.30                                    23.55
    649      2.30                                    23.55
    555      2.30                                    23.55
    555      2.30                                    23.55
    */
    
    
    Select right(Rtrim(a_object), 3) a_object,  
    sum (bud_req_amt2) as TOTAL1,
    sum (bud_req_amt3) as TOTAL2 
    from budget 
    where type ='E' 
    group by right(Rtrim(a_object), 3)
    /*
    a_object TOTAL1                                  TOTAL2
    -------- --------------------------------------- ---------------------------------------
    555      4.60                                    47.10
    562      4.60                                    47.10
    649      2.30                                    23.55
    */
    
    ;with cte as (
    Select right(Rtrim(a_object),3) a_object,  
    sum (bud_req_amt2) as TOTAL1 ,
    sum (bud_req_amt3) as TOTAL2 
    from budget where type ='E' 
    Group by a_object
    )
    select  a_object,
    sum (TOTAL1) as TOTAL1,
    sum (TOTAL2) as TOTAL2 
    from cte
    group by a_object;
    /*
    a_object TOTAL1                                  TOTAL2
    -------- --------------------------------------- ---------------------------------------
    555      4.60                                    47.10
    562      4.60                                    47.10
    649      2.30                                    23.55
    */

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Ashin_c Thursday, August 22, 2019 8:36 AM
    Monday, August 19, 2019 8:25 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 20, 2019 9:26 AM
  • Hello

    No because I have 3 table related to this and the group by is not allow

    Tuesday, August 20, 2019 11:15 AM
  • Hi, 

    Could you please share us more information (table structure and your actual script) and show us your error message?

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 21, 2019 6:41 AM

  • No because I have 3 table related to this and the group by is not allow

    In your original post, you only had one table. Are you saying that you also have a query with three tables? Are you expecting us to help you with that query, despite that we don't know how it looks like?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, August 21, 2019 11:57 AM