locked
Issue in SQL Group by Query RRS feed

  • Question

  • User759651737 posted
    Actually, I want to have a subtotal by grouping the First names, but displaying the Project name, project id and alternative proj_name of  a employee..which will change.

    
    
    Hi, I am trying to write a Store Proc to generate a report. But, the below query gives me 4 rows of each item. I want subtotal of all the months grouped by persons first name, but at the same time, I want to display 
    the Project name, project id and alternative proj_name of all the individual projects as well.
    I am getting 4 rows in which the same output is repeated, with nulls in other three firleds (Project name, project id and alternative proj_name ).
    This is not my intended output.... please help!



    Output produced
    Fname Pr.name    Dz    ZZZZ    15.71    15.71    15.71    15.71    15.71    15.71    15.71    0.00    0.00    0.00    0.00    0.00
    Fname Pr.name    Dz    NULL    15.71    15.71    15.71    15.71    15.71    15.71    15.71    0.00    0.00    0.00    0.00    0.00
    Fname Pr.name    NULL    NULL    15.71    15.71    15.71    15.71    15.71    15.71    15.71    0.00    0.00    0.00    0.00    0.00
    Fname SUB TOTAL    NULL    NULL    15.71    15.71    15.71    15.71    15.71    15.71    15.71    0.00    0.00    0.00    0.00    0.00


    Desired Output
    Fname Pr.name    Dz    ZZZZ    15.71    15.71    15.71    15.71    15.71    15.71    15.71    0.00    0.00    0.00    0.00    0.00
    Fname SUB TOTAL    NULL    NULL    15.71    15.71    15.71    15.71    15.71    15.71    15.71    0.00    0.00    0.00    0.00    0.00



    
    
    SELECT   B.first, isnull(convert(VARCHAR, C.[project_name]), 'SUB TOTAL') AS [project_name],  
                                  isnull(convert(VARCHAR, D.[name]), [name]) AS [name],
                      isnull(convert(VARCHAR, C.[project_id]), [project_id]) AS [project_id] ,

     sum(A.Jan_2012_Days) as Jan_2012_Days , sum([Feb_2012_Days]) as Feb_2012_Days, sum([Mar_2012_Days]) as Mar_2012_Days, sum([Apr_2012_Days]) as Apr_2012_Days, sum([May_2012_Days]) as May_2012_Days, sum([June_2012_Days]) as June_2012_Days, sum([July_2012_Days]) as July_2012_Days, sum([Aug_2012_Days]) as Aug_2012_Days, sum([Sep_2012_Days]) as Sep_2012_Days, sum([Oct_2012_Days]) as Oct_2012_Days, sum([Nov_2012_Days]) as Nov_2012_Days, sum([Dec_2012_Days]) as Dec_2012_Days FROM pim.dbo.[resource_forecast] A, pim.dbo.[employee] B, pim.dbo.[new_project] C, pim.dbo.[roles] D where A.[employee_id] = B.[emp_id] and A.[PR_ID] = C.[PR_ID] and A.[role] = D.[role_id] GROUP BY B.[first], [project_name],[name], [project_id] with rollup ORDER BY B.[first]

    Monday, January 9, 2012 8:47 PM

Answers

  • User522486851 posted

    Hi Try the below query with your parameters

    SELECT max(Project_Name), max(project_id),max(alternative_proj_name)
    First_Name, Sum([FieldName])
    FROM [YourTableName]
    GROUP BY First_Name
     
    If this is not working please post your query here
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 9, 2012 10:26 PM

All replies

  • User522486851 posted

    Hi Try the below query with your parameters

    SELECT max(Project_Name), max(project_id),max(alternative_proj_name)
    First_Name, Sum([FieldName])
    FROM [YourTableName]
    GROUP BY First_Name
     
    If this is not working please post your query here
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 9, 2012 10:26 PM
  • User-1133898271 posted

    Try to use the Distinct keyword based on first name or project_id sure u will get 

    Tuesday, January 10, 2012 12:07 AM
  • User759651737 posted

    Using Max did work..here is my query. ( I didnt try Distinct)

    SELECT   
    isnull(convert(VARCHAR, [project]), 'tot') AS [project],
    isnull(convert(VARCHAR, [name]), 'sub tot) AS [name],
    max([project_id]) as [project_id]
     sum(days) as Days
    FROM table A
    
    GROUP BY  [project], [name]  with rollup  
    ORDER BY  [project] ;
    Tuesday, January 10, 2012 3:38 PM