Issue in SQL Group by Query RSS

• • Reply
  

  irah123

  Member

  18 Points

  16 Posts

  Issue in SQL Group by Query

  Jan 10, 2012 12:47 AM|LINK

  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] 

  • Edited by irah123 on Jan 10, 2012 12:48 AM
  • Edited by irah123 on Jan 10, 2012 12:48 AM
  • Edited by irah123 on Jan 10, 2012 12:49 AM
  • Edited by irah123 on Jan 10, 2012 12:49 AM
  • Edited by irah123 on Jan 10, 2012 01:17 AM
• • Reply
  

  Sankalpa

  Contributor

  2134 Points

  494 Posts

  Re: Issue in SQL Group by Query

  Jan 10, 2012 02:26 AM|LINK

  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

  Please mark the replies as answers if it help.
  • Marked as answer by irah123 on Jan 10, 2012 07:36 PM
• • Reply
  

  sairam62.cse

  Participant

  989 Points

  309 Posts

  Re: Issue in SQL Group by Query

  Jan 10, 2012 04:07 AM|LINK

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

  If The Answer is Satisfied Plz Select Mark As Answer in my Place,
  s.sairam
• • Reply
  

  irah123

  Member

  18 Points

  16 Posts

  Re: Issue in SQL Group by Query

  Jan 10, 2012 07:38 PM|LINK

  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] ;

  • Edited by irah123 on Jan 10, 2012 07:40 PM