Group by and having question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by and having question

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2009-03-13 : 10:45:11

I am trying to find all companies that do not have a primary contact. The information is stored like below. I will need to do a count for primary contact and group by CompanyID.

Id CompanyId PrimaryContact
101 201 1
103 201 1
104 202 1
105 202 0
106 203 0
107 203 0

I can get the companies that have an id count of more than one, but cannot figure out how to make my criteria work in teh same manner.

SELECT CO_ID
FROM demo_membership
inner join name
on name.id = demo_membership.id
GROUP BY co_ID having count (*) >= 1

Thanks,
dz

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-13 : 11:03:37

SELECT CO_ID
FROM demo_membership
inner join name
on name.id = demo_membership.id
GROUP BY co_ID having sum(cast(primarycontact as int))=0

dzabor
Posting Yak Master

138 Posts

Posted - 2009-03-13 : 11:46:28

This returns the companies that have a primary.

I need it to show me the co_id and a count of the primary records OR just the companies with NO primary contact.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-13 : 12:43:27

Can you provide an example of the output you want, using the sample data posted?

quote:
I need it to show me the co_id and a count of the primary records OR just the companies with NO primary contact

These are contradictory conditions, unless you UNION the two queries together.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 12:52:23

quote:
Originally posted by dzabor

This returns the companies that have a primary.

I need it to show me the co_id and a count of the primary records OR just the companies with NO primary contact.


SELECT CompanyId,
SUM(CASE WHEN PrimaryContact=1 THEN 1 ELSE 0 END)
FROM table

dzabor
Posting Yak Master

138 Posts

Posted - 2009-03-13 : 13:58:27

The output would look like this:

CompanyId Count of Primary
101 2
102 1
103 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 13:59:53

i think my query will do the job

dzabor
Posting Yak Master

138 Posts

Posted - 2009-03-13 : 14:07:37

That works! I just needed to add the group by.

SELECT co_Id,
SUM(CASE WHEN Primary_Contact=1 THEN 1 ELSE 0 END)
FROM demo_membership
inner join name
on name.id = demo_membership.id
group by co_id

Thanks for letting me end my Friday on a good note!

dx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 14:20:04

welcome
actually i missed group by while pasting code. glad that you spotted it out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources