Malinda Klein
asked on
How to filter out rows with more than 1 group?
In SSRS, I want to filter out only those employees with more than 1 partfile number.
My query is
Select e.name,
p.pfnumber from employees as e
left join partfile as p
on p.recid=e.partfilerecid
group by e.name
What expression do I use in the filter to be > 1?
My query is
Select e.name,
p.pfnumber from employees as e
left join partfile as p
on p.recid=e.partfilerecid
group by e.name
What expression do I use in the filter to be > 1?
Select e.name,
max(p.pfnumber) as pfnumber
from dbo.employees as e
left join dbo.partfile as p
on p.recid=e.partfilerecid
group by e.name
having count(*) <= 1
max(p.pfnumber) as pfnumber
from dbo.employees as e
left join dbo.partfile as p
on p.recid=e.partfilerecid
group by e.name
having count(*) <= 1
ASKER
Actually I was wanting to filter out those employees that have two or more pfnumbers so I need to count the number of pfnumbers and only display those that have two or more. But SSRS won't let me list an aggregate in the expression.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER