• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

Two different where clauses in the same row...

Table WorkLog = id, projectName, IsSupport, hours, logNotes

I need the following result

"Project"       - Distinct Project Name,
"Total Hours"   - sum of hours for the project name,
"Support Hours" - sum of hours for the project name where IsSupport = 1


So,
1, 'www.site...', 0, 10, 'asdfasdfasdf'
2, 'www.site...', 0, 10, 'qwerqwerqwer'
3, 'www.site...', 1, 10, 'zxcvzxcvzxcv'
4, 'www.other..', 0, 20, '123412341234'

would return this:
Project            Total Hours      Support Hours
'www.site...'      30            10
'www.other..'      20            0

I was thinking maybe I could do an inner join against itself (on wl1.id = wl2.id), and where values on each.
0
hpdvs2
Asked:
hpdvs2
  • 2
2 Solutions
 
deightonprogCommented:
SELECT Project, SUM(Hours) AS TotalHours, SUM(CASE WHEN isSupport = 1 THEN Hours ELSE 0 END) AS SupportHours
GROUP BY Project
0
 
tim_csCommented:
SELECT
   Project
   ,SUM(Hours) TotalHours
   ,SUM(CASE WHEN IsSupport = 1 THEN Hours ELSE 0 END) SupportHours
FROM
   WorkLog
GROUP BY
   Project
0
 
deightonprogCommented:
SELECT Project, SUM(Hours) AS TotalHours, SUM(CASE WHEN isSupport = 1 THEN Hours ELSE 0 END) AS SupportHours
from yourTable
GROUP BY Project
0
 
hpdvs2Author Commented:
Thanks,  I've never used a CASE command in a function call before.  Most useful.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now