How to return multiple rows in sql from same table

Question

Pretty new here and could really use some help. I have a table with

TimeID(pk), DateEntry(date), EntryTiem(time), 
ProjID(int), ProjName(varchar), Phone(bit), 
Research(bit), Notes(varchar), ProjActive(bit), 
TimeDateStamp(date).

I'm trying to return multiple rows of data into each ProjName but am not having any luck. Here is what I've come up with so far...

SELECT ProjName AS 'Project Name',  
    (SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
     + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)
     FROM WorkTime
     WHERE Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL
     ) AS 'Total Time No PMRE',     
    (SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2) 
     + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2) 
     FROM WorkTime
     WHERE Phone = 1 AND RESEARCH = 0 AND EntryTime IS NOT NULL
     ) AS 'Total Time Phone',
     (SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
      + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)  
      FROM WorkTime
      WHERE Phone = 0 AND RESEARCH = 1 AND EntryTime IS NOT NULL
      ) AS 'Total Time Research',
      (SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
       + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2) 
       FROM WorkTime
       WHERE EntryTime IS NOT NULL
       ) AS 'TotalTime'
FROM WorkTime
WHERE EntryTime IS NOT NULL
GROUP BY ProjName

Which returns the following...

Project Name   Total Time No PMRE   Total Time Phone   Total Time Research   Total Time
AAAA           19:06:15             2:00:00            1:00:06               22:06:21
BBBB           19:06:15             2:00:00        1:00:06               22:06:21
CCCC           19:06:15             2:00:00        1:00:06               22:06:21
DDDD           19:06:15             2:00:00        1:00:06               22:06:21

Which is wrong. The amount in each column is the total for that condition with all of the ProjName added together instead of separated out individually.

This is what the output should be...

Project Name   Total Time No PMRE   Total Time Phone   Total Time Research   Total Time
AAAA           00:00:19             0:00:00            0:00:00               00:00:19
BBBB           00:00:04             0:00:00        0:00:00               00:00:04
CCCC           03:00:00             2:00:00        1:00:06               06:06:06
DDDD           16:05:52             0:00:00        0:00:00               16:05:52

Does anyone have any suggestions or could help point me in the right direction? Thanks!

SELECT ProjName AS 'Project Name' ,[Total Time No PMRE] = CONVERT(VARCHAR(10), SUM(CASE WHEN Phone = 0 AND Research = 0 THEN (DATEDIFF(MINUTE, 0, EntryTime))/3600 END)) ,[Total Time Phone] = CONVERT(VARCHAR(10), SUM(CASE WHEN Phone = 1 AND Research = 0 THEN (DATEDIFF(MINUTE,0, EntryTime)) END)) ,[Total Time Research] = CONVERT(VARCHAR(10), SUM(CASE WHEN Phone = 0 AND Research = 1 THEN (DATEDIFF(MINUTE, 0,EntryTime)) END)) ,[TotalTime] = CONVERT(VARCHAR(10), SUM(DATEDIFF(MINUTE, 0, EntryTime))) FROM WorkTime WHERE EntryTime IS NOT NULL GROUP BY ProjName — Jeremy Dodt
– Jeremy Dodt, Commented May 14, 2013 at 15:12
I got it to work using the above query but now I am having trouble getting it to convert the times and when I try to format I get the following message... Msg 130, Level 15, State 1, Line 3 Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Any suggestions. Also, thanks to everyone for the guidance! — Jeremy Dodt
– Jeremy Dodt, Commented May 14, 2013 at 15:13
@Agustine - Thanks for the help. With the information you posted along with some additional searching and help I was able to get it figured out. Ended up using a conditional sum and a CTE and it worked along with the formatting. Thanks again. — Jeremy Dodt
– Jeremy Dodt, Commented May 14, 2013 at 17:39

Agustin Meriles · Accepted Answer · 2013-05-14 18:19:39Z

1

I think that what you are looking for is a conditional SUM:

SELECT ProjName AS 'Project Name',  
    SUM(CASE WHEN (Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time No PMRE',
    SUM(CASE WHEN (Phone = 1 AND RESEARCH = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time Phone',
    SUM(CASE WHEN (Phone = 0 AND RESEARCH = 1 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time Research',
    SUM(DATEDIFF(second, 0, EntryTime)) AS 'TotalTime'
FROM WorkTime
WHERE EntryTime IS NOT NULL
GROUP BY ProjName

Add your formatting code to the result of SUM and you will be fine

Edit

Answering your comment for the format, take your format "template":

CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
 + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)

and replace every occurence of SUM(DATEDIFF(second, 0, EntryTime) for SUM(CASE WHEN (Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END)

Or, you can add the formats in a query that wraps the source query, like this:

SELECT CAST([Total Time No PMRE] / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST([Total Time No PMRE] / 60 % 60 AS varchar(2)),2)
    + ':' + RIGHT('0' + CAST(([Total Time No PMRE] % 60 AS varchar(2)), 2)
... -- The other columns
FROM (
    SELECT ProjName AS 'Project Name',
        SUM(CASE WHEN (Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time No PMRE',
        SUM(CASE WHEN (Phone = 1 AND RESEARCH = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time Phone',
        SUM(CASE WHEN (Phone = 0 AND RESEARCH = 1 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time Research',
        SUM(DATEDIFF(second, 0, EntryTime)) AS 'TotalTime'
    FROM WorkTime
    WHERE EntryTime IS NOT NULL
    GROUP BY ProjName
 ) AS ds

Hope this helps you

edited May 14, 2013 at 18:19

answered May 13, 2013 at 21:56

Agustin Meriles

4,8603 gold badges32 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeremy Dodt Over a year ago

I get the following message when I run this query... Msg 8117, Level 16, State 1, Line 2 Operand data type time is invalid for sum operator.

Jeremy Dodt Over a year ago

Thanks Agustin, I appreciate it. Unfortunately, I get an error message when I try to add the formatting code."Cannot perform an aggregate function on an expression containing an aggregate or a subquery." I was able to play around and came up with something very similar to what you have here(see comment I posted earlier with solution) but run into the same problem with formatting and error code. I appreciate the help. Any ideas on the format?

Tim Lehner · Accepted Answer · 2013-05-13 21:54:37Z

You need to correlate these subqueries to something in the outer query, probably the same thing you're grouping on:

SELECT ProjName AS 'Project Name',  
    (
        SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
        + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)
        FROM WorkTime
        WHERE Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL
            AND ProjName = a.ProjName -- Correlate to outer query
    ) AS 'Total Time No PMRE',     
    (
        SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2) 
        + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2) 
        FROM WorkTime
        WHERE Phone = 1 AND RESEARCH = 0 AND EntryTime IS NOT NULL
            AND ProjName = a.ProjName -- Correlate to outer query
    ) AS 'Total Time Phone',
    (
        SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
        + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)  
        FROM WorkTime
        WHERE Phone = 0 AND RESEARCH = 1 AND EntryTime IS NOT NULL
            AND ProjName = a.ProjName -- Correlate to outer query
    ) AS 'Total Time Research',
    (
        SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
        + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2) 
        FROM WorkTime
        WHERE EntryTime IS NOT NULL
            AND ProjName = a.ProjName -- Correlate to outer query
    ) AS 'TotalTime'
FROM WorkTime a -- Add alias
WHERE EntryTime IS NOT NULL
GROUP BY ProjName

Collectives™ on Stack Overflow

How to return multiple rows in sql from same table

2 Answers 2

Edit

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Edit

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related