Repeat aggregate field for each difference

Terrible title but couldn't think how to word it!
ok if I do
count(id), id from...... having count(id) > 2
I get my results fine e.g.
4   123
3   456

Now if I want to include another field that will not be the same for each row (as it was for ID) I get zero results
count(id), id, name from.... group by..... having count(id) > 2
How can I get my results like this
4   123    John
4   123    Fred
4   123    David
4   123    Jane
3   456    Tim
3   456    Kate
3   456    Paul

This is destined for a SSRS report so I would be ultimately aiming for it to look like

4   123   John
               Fred
               David
               Jane
LVL 29
QPRAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

miketobCommented:
What I would do is to write the query without grouping or sumarizing (Select id, name from Table) and then, do the counting and filtering within SSRS.
0
chaauCommented:
I would use the query as is. SSRS provides a better way to suppress repeating values using its built-in Previous() function. Just use a simple expression for the values:
=IIf(Previous(Fields!Id.Value) = Fields!Id.Value, Nothing, Fields!Id.Value)

Open in new window

If you implement the grouping in SSRS it is even easier. There is a property for the textboxes called "HideDuplicates". If you set it to true it will generate the desired report
0
QPRAuthor Commented:
I need to include my count as I'm not interested in the rows where the count is < 2
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

miketobCommented:
Yes, but you can do the count in SSRS and then hide the groups where the count is <2
0
QPRAuthor Commented:
Surely there is a t-SQL way: cursor, inner query, cte? Rather than me having to carry 6,000 unwanted rows to the report?
0
chaauCommented:
I did not suggest you changing the SQL. I just said that you can suppress the duplicate values in the SSRS. Try it
0
QPRAuthor Commented:
I can't try it because as soon as I include a column that will return differing values I get zero results which was my question. The SSRS bit was additional info
0
chaauCommented:
OK, I see now where your problem is. You need to use a different query. It looks like you want the count based on the ID, but at the same time you want to include the names. Therefore your count should come from your original query:
count(id), id from...... having count(id) > 2 

Open in new window

Let's make the query complete. Let's assume your table is in fact called yourTable:
select count(id), id from yourTable GROUP BY ID having count(id) > 2 

Open in new window

Now, if you add the name your way:
select count(id), id, name from yourTable GROUP BY ID, name having count(id) > 2 

Open in new window

The count will be grouped by ID, name. And as your attempt showed there are "zero results" here.
To bring the names you need to join, like this:
select c.cnt, c,id, n.name
from (select count(id) cnt, id from yourTable GROUP BY ID having count(id) > 2) as c
INNER JOIN yourTable n ON c.id = n.id

Open in new window

Try it and let us know how it goes
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QPRAuthor Commented:
Thanks am away from the office for the day now, will give it a go in the morning (local time) and report back
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Use a Common Table Expression (CTE):
;WITH CTE_GrpId (ID, CountID)
AS (
	SELECT ID, COUNT(ID)
	FROM TableName
	GROUP BY ID
	HAVING COUNT(ID) > 2
	)
SELECT g.*, t.Name
FROM CTE_GrpId g
	INNER JOIN TableName t ON t.ID = g.ID

Open in new window

0
Scott PletcherSenior DBACommented:
having count(id) > 2
I'm not interested in the rows where the count is < 2

Do you want count(id) > 2 or >= 2?  The second is much easier to do without having to use a CTE:

SELECT tn.Id, tn.Name, ...
FROM table_name tn
WHERE EXISTS(SELECT 1 FROM table_name tn2 WHERE tn2.id = tn.id AND tn2.name <> tn.name)
0
awking00Information Technology SpecialistCommented:
select * from
(select count(*) over (partition by id) as cnt, id, name
 from table_name) x
where cnt > 2; ==> or > 1 if you want >= 2
0
QPRAuthor Commented:
With all these great suggestions I don't know which is the best one to follow :)
I've pasted the code below so that we don't have any unknown factors.
I want a count of more than 2 (not 2 so either >2 or >=3 works for me)

select count(key2), key2, Inf.INFRINGEMENT_type
, Inf.INFRINGEMENT_NUMBER as "Inf Number"
, convert(varchar, Inf.infringement_date, 104) as "Infringement Date"
from infHeader Inf
inner join nucassociation Ass
	on Ass.key1 = Inf.INFRINGEMENT_CTR
where Ass.association_type = 'infAnimal'
	and Inf.INFRINGEMENT_TYPE = 'DOG'
group by key2, Inf.INFRINGEMENT_type
having count(Ass.key2) > 2

Open in new window

0
QPRAuthor Commented:
I'm trying the CTE method but am not getting far as I need to inner join on key1 which comes from the table that is within the CTE definition... but then when I include this column I get back to my zero results issue.
Based on my query syntax above can somebody suggest the best method?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
QPR, can you provide the sample data that you're working with?
0
QPRAuthor Commented:
I provided a sample table above, other than that it is just some numbers.
0
awking00Information Technology SpecialistCommented:
>>I provided a sample table above<<
What you seem to have provided was a query result and not a table. What the experts would really like to see is some sample data for the key2, key1, and association_type fields from the nucassociation table and infringement_type, infringement_number, infringement_date, and infringement_ctr from the infHeader table along with your expected output from that data.
0
QPRAuthor Commented:
Managed to get what I needed based on this idea thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.