CODEWITHSUNDEEP
sql-server
pfinferno
pfinferno

Reputation: 1945

Select column where another related column's total is 0

I have a table called xDays set up like below:

╔══════════════╦═════════════════════════╦═══════╗
║ Project_Name ║          Date           ║ Hours ║
╠══════════════╬═════════════════════════╬═══════╣
║ proj1        ║ 2010-03-03 00:00:00.000 ║     0 ║
║ proj1        ║ 2010-03-04 00:00:00.000 ║     0 ║
║ proj1        ║ 2010-03-05 00:00:00.000 ║     0 ║
║ proj2        ║ 2010-03-03 00:00:00.000 ║     1 ║
║ proj2        ║ 2010-03-04 00:00:00.000 ║     0 ║
║ proj2        ║ 2010-03-05 00:00:00.000 ║     0 ║
╚══════════════╩═════════════════════════╩═══════╝

What I'm trying to do is select each Project_Name who's total in the Hours column is 0. In the above example, the select statement should return proj1, since the total hours for it is 0, whereas the total hours for proj2 is 1.

The closest I got was:

select sum(hours) as 'total', project_name
From xDays
group by project_name
order by project_name

This gives me a table showing the total hours for each project_name which DOES show 0 total hours for certain project_names. From here, I've tried a few different things and either get a

Conversion failed when converting the varchar value to datatype int

error or empty results. Some examples of what I've tried:

 select sum(hours) as 'total', project_name
 From xDays
 where 'total' = convert(varchar(10), 0)
 group by project_name
 order by project_name`

This returns empty results.

select sum(hours) as 'total', project_name
From xDays
where 'total' = 0
group by project_name
order by project_name

This returns a conversion error (cannot convert varchar total to int).

How can I get this working properly?

Upvotes: 2

Views: 50

Answers (4)

JohnLBevan
JohnLBevan

Reputation: 24440

Assuming all projects you want to report on are in the xDays table, @PanagiotisKanavos's answer's best.

Special Scenarios / Considerations

If it's possible to have projects which don't have any entries in the xDays table, but you still want those returned (i.e. since by not being in that table it's implied that no hours were spent on them).

select project_name
from Projects
where project_name not in
(
    select distinct project_name
    from xDays
    where xdays.hours != 0
)

If it's possible to have negative hours, and you want those projects where the total hours on that project equals zero (i.e. where negatives cancel positives):

select project_name
from Projects
where project_name not in
(
    select project_name
    from xDays
    group by project_name
    having sum(xdays.hours) != 0
)

Upvotes: 2

Preston
Preston

Reputation: 8187

Try this:

select project_name
from xDays
group by project_name
having sum(xdays.hours) = 0

You will need to utilize tha having clause to filter based on an aggregate total

Upvotes: 1

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131533

You can filter aggregate results with the HAVING clause:

select sum(hours) as 'total'
From xDays
group by project_name
HAVING sum(hours) = 0
order by project_name

The HAVING clause can only appear after the GROUP BY clause and can't use any column alias

Upvotes: 4

John Cappelletti
John Cappelletti

Reputation: 81990

Try the following

Select Project_Name
 From  xDays
 Group By Project_Name
 Having Sum(Hours)=0

Upvotes: 2

Related Questions