Solved

Two different where clauses in the same row...

Posted on 2011-09-27
4
187 Views
Last Modified: 2012-05-12
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
Comment
Question by:hpdvs2
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
deighton earned 250 total points
ID: 36709695
SELECT Project, SUM(Hours) AS TotalHours, SUM(CASE WHEN isSupport = 1 THEN Hours ELSE 0 END) AS SupportHours
GROUP BY Project
0
 
LVL 15

Assisted Solution

by:tim_cs
tim_cs earned 250 total points
ID: 36709699
SELECT
   Project
   ,SUM(Hours) TotalHours
   ,SUM(CASE WHEN IsSupport = 1 THEN Hours ELSE 0 END) SupportHours
FROM
   WorkLog
GROUP BY
   Project
0
 
LVL 18

Expert Comment

by:deighton
ID: 36709712
SELECT Project, SUM(Hours) AS TotalHours, SUM(CASE WHEN isSupport = 1 THEN Hours ELSE 0 END) AS SupportHours
from yourTable
GROUP BY Project
0
 
LVL 8

Author Closing Comment

by:hpdvs2
ID: 36709742
Thanks,  I've never used a CASE command in a function call before.  Most useful.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question