Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Two different where clauses in the same row...

Posted on 2011-09-27
Medium Priority
194 Views
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
Question by:hpdvs2
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 18

Accepted Solution

deighton earned 1000 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

tim_cs earned 1000 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

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

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

## Featured Post

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
###### Suggested Courses
Course of the Month8 days, 13 hours left to enroll