How to get totals... 1

[robertfah]

I've got a SQL statement that returns the following rows:

CCode TotalTime
AATC 22
ACG 60
ACG 30
ACG 1480
ACG 22
AFL 50
ASA 20

I would like to know how I can sum up the TotalTime for each CCode and only show that, so it would look like:

CCode TotalTime
AATC 22
ACG 1592
AFL 50
ASA 20

How can this be done? I would post the SQL but it has some joins and the field names are not very friendly so I just changed them in here to make more sense.....but I can post it if necessary.

 

[r937]

ok, you have a working SQL statement

do this --

SELECT CCode   
     , SUM(TotalTime) AS TotalTime
  FROM (
     [red]put your working SQL statement here[/red]
       ) AS t
GROUP
    BY CCode

vwalah

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[robertfah]

Thanks, but I seem to be having some issues....SQL won't let me do a SUM on a SUM....I get the error:

Server: Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

So here's my SQL Statement:

SELECT CO_COMPANY_CODE, SUM(ia.IAC_TIME_SPENT) + convert(int,i.IS_ISSUE_TIME) AS TotalTimeSpent
FROM ISSUES i 
INNER JOIN ISSUE_ACTIVITIES ia ON i.IS_ISSUE_NO = ia.IAC_ISSUE_NO
INNER JOIN COMPANIES c ON i.IS_COMPANY_ID = CO_COMPANY_ID
WHERE (i.IS_ISSUE_NO IN (SELECT DISTINCT(i2.IS_ISSUE_NO)
                         FROM ISSUES i2
			 WHERE (dbo.FloatToDateTime(IS_UPDATE_DATETIME) BETWEEN '3/31/2011 23:59:59' AND '5/1/2011 00:00:01') 
			 OR (dbo.FloatToDateTime(IS_RECEIVED_DATETIME) BETWEEN '3/31/2011 23:59:59' AND '5/1/2011 00:00:01')))
AND ia.IAC_ACTIVITY_TYPE NOT IN ('BAE Action')
GROUP BY CO_COMPANY_CODE, is_issue_time
ORDER BY CO_COMPANY_CODE

In this, CO_COMPANY_CODE is the CCode and the TotalTime is TotalTimeSpent. How can I get around SUMing a SUM?

 

[r937]

SELECT co_company_code
     , SUM(TotalTimeSpent) AS TotalTimeSpent
  FROM ( [red]SELECT c.co_company_code
              , SUM(ia.iac_time_spent) + 
                CONVERT(INT,i.is_issue_time) 
                   AS TotalTimeSpent
           FROM issues i 
         INNER 
           JOIN issue_activities ia 
             ON ia.iac_issue_no = i.is_issue_no 
         INNER 
           JOIN companies c 
             ON c.co_company_id = i.is_company_id 
          WHERE i.is_issue_no IN 
                ( SELECT DISTINCT is_issue_no
                    FROM issues     
                   WHERE dbo.FloatToDateTime(is_update_datetime) 
                         BETWEEN '3/31/2011 23:59:59' 
                             AND '5/1/2011 00:00:01'
                      OR dbo.FloatToDateTime(is_received_datetime)
                         BETWEEN '3/31/2011 23:59:59' 
                             AND '5/1/2011 00:00:01' )
            AND ia.iac_activity_type <> 'BAE Action'
         GROUP 
             BY c.co_company_code
              , is_issue_time[/red]
         ) AS t
GROUP
    BY co_company_code

the part in red is your original SQL statement, i just cleaned it up a bit

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[robertfah]

Thanks r937...this worked great!