Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a very simple SQL Query which is getting results in the following way

COLUMN A COL-B

WINDCHILL   99.998889 
WINDCHILL   96.000000

Now how i can modify my query so that I get the total in a single line?
COL-A COL-B

WINDCHILL   195.99 

The necessary query is as;

 SELECT 
       ISNULL([Task IT Assets], [Project IT Asset]) AS Asset ,
       SUM(P.ActualWork) AS taskHours
 FROM
      [IR.TimesheetHoursProjectTask] p 
 WHERE
      p.ActualWork > 0 AND 
      ([Task IT Assets] IS NOT NULL OR [Project IT Asset] IS NOT NULL) 
 GROUP BY 
      ISNULL([Task IT Assets], [Project IT Asset]), ActualWork 
 ORDER BY
      p.ActualWork DESC 
share|improve this question
    
It's not easy to understand what you're asking here. Are you just trying to roll up the contents of a column (GROUP BY) or pivot a series of values across a row (PIVOT) - could you please re-word your question to clarify it? –  ConcernedOfTunbridgeWells Apr 19 '12 at 10:51
add comment

2 Answers 2

up vote 4 down vote accepted

You need to modify the GROUP BY and the ORDER BY clauses.

Try this:

SELECT 
       ISNULL([Task IT Assets], [Project IT Asset]) AS Asset ,
       SUM(P.ActualWork) AS taskHours
 FROM
      IR.TimesheetHoursProjectTask p 
 WHERE
      p.ActualWork > 0 AND 
      ([Task IT Assets] IS NOT NULL OR [Project IT Asset] IS NOT NULL) 
 GROUP BY 
      ISNULL([Task IT Assets], [Project IT Asset]) 
 ORDER BY
      SUM(P.ActualWork) DESC 

As an aside, note that your table is literally named "IR.TimesheetHoursProjectTask" and not "TimesheetHoursProjectTask" under the "IR" schema. To specify the later, using the [] notation, it would be [IR].[TimesheetHoursProjectTask].

share|improve this answer
add comment

I think you need to remove the ActualWork field in Group By.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.