totals including 0's 1

[Pattycake245]

I am trying to do a join which will summarize the number of times each employee has met with a supervisor. I also want to show those supervisors who have not met with a particular employee as having counts of 0. Something like this:

emp supr
bob frank
bob jim
bob frank
fern frank
fern mike
fern mike


So in summarizing, it would look like this:

emp supr cnt
bob frank 2
bob jim 1
bob mike 0
fern frank 1
fern jim 0
fern mike 2

I thought of making a table with all the supervisors and joining on that but I still can't get the records to show where the employee has not met with a supervisor.

Tim

 

[mlibeson]

Try something like the following:

SELECT EMP, SUPR, ISNULL(COUNT(SUPR),0) FROM TABLENAME
GROUP BY EMP, SUPR
ORDER BY EMP, SUPR

Michael Libeson

 

[Pattycake245]

No, this will never get me a list of the two employees with counts for all supervisors.

You'll notice by the raw data that Bob has not met with mike, and fern has not met with jim and I want to show that by having counts of 0 for those two situations. I am almost positive I need some kind of table listing all possible suprevisors to join on.

 

[vongrunt]

With supervisor/employee tables this could be much easier (this is basically n:n table). Without them things get ugly:

select E.emp, S.supr, isnull(sum(T.howmany), 0) as cnt
from 
(	select distinct emp  from mytable ) E
cross join
(	select distinct supr from mytable ) S
left join 
(	select emp, supr, 1 as howmany from myTable ) T on E.emp=T.emp and S.supr=T.supr
group by E.emp, S.supr
order by E.emp, S.supr

 

[maswien]

try this:

select
t2.emp,
t2.supr,
sum(case when t3.supr is null then 0 else 1 end)
from
(select t0.emp, t1.supr
from
(select distinct emp from employee) t0,
(select distinct supr from employee) t1
) t2 left outer join employee t3
on t2.emp = t3.emp and t2.supr = t3.supr

group by t2.emp,
t2.supr

 

[Pattycake245]

Vongrunt, that is brilliant!! Yes unfortunately this queries results all stored in one table. I didn't think that it would be so involved. I've done queries like this using only one field to group by , but I guess as you add extra fields the difficulty increases 10-fold.

thanks so much!

Tim

 

[mlibeson]

Sorry about that. I was a little quick on that response. You will never have 0's as it only groups on data that exists.


Michael Libeson

 

[maswien]

The following function will return the wrong results because anything sum with null will be null, so the
values lost.

isnull(sum(T.howmany), 0)

 

[maswien]

Please try my SQL

 

[donutman]

   [Blue]SELECT[/Blue] C.Emp[Gray],[/Gray] C.Supr[Gray],[/Gray] [Fuchsia]IsNull[/Fuchsia][Gray]([/Gray]Meetings[Gray],[/Gray]0[Gray])[/Gray] Meetings
      [Gray]([/Gray][Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] Supr [Blue]FROM[/Blue] MyTable[Gray])[/Gray] M
   [Fuchsia]Left[/Fuchsia] [Blue]JOIN[/Blue]
      [Gray]([/Gray][Blue]SELECT[/Blue] Emp[Gray],[/Gray] Supr[Gray],[/Gray] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray]Supr[Gray])[/Gray] Meetings
          [Blue]FROM[/Blue] MyTable [Blue]GROUP[/Blue] [Blue]BY[/Blue] Emp[Gray],[/Gray] Supr[Gray])[/Gray] C
   [Blue]ON[/Blue] M.Supr[Gray]=[/Gray]C.Supr
   [Blue]ORDER[/Blue] [Blue]BY[/Blue] C.Emp[Gray],[/Gray] C.Supr

This assumes that there are multiple employees/supervisors with the same name.
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mlibeson]

Change:

isnull(sum(T.howmany), 0)

TO:

isnull(sum(isnull(T.howmany,0)), 0)

And it will work as vongrunt has instructed you properly.


Michael Libeson

 

[maswien]

mlibeson, the outer isnull() is not needed.

 

[vongrunt]

The following function will return the wrong results because anything sum with null will be null, so the
values lost.

Not sure... Aggregate functions ignore NULLs, right?

 

[SQLBill]

vongrunt,

Yes, NULLs are ignored in aggregates.

BOL, use Index tab enter NULL Values, then choose the first option (aggregate functions).

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875