count() help - 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

count() help

Author

Topic

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-01 : 08:34:14

Hello,

I have table SalesTable:

SalesId CustAccount
1 15
2 18
3 17
4 15
5 15

I need to find all SalesId's that have more than 1 CustAccount, basically need a query to return

1
4
5

But I can't hardcode anything like:


SELECT CUSTACCOUNT FROM SALESTABLE WHERE CUSTACCOUNT = '15'

I've tried:


SELECT SALESTABLE.SALESID FROM SALESTABLE 
where SALESTABLE.CUSTACCOUNT = 
(select CUSTACCOUNT from SALESTABLE group by CUSTACCOUNT
				having COUNT(custaccount) > 1		)

But obviously the sub query returns more than 1 value which is not permitted.

Any assistance greatly appreciated.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-01 : 08:38:14

[code]
declare @t table
(
SalesId int,
CustAccount int
)

insert @t
select 1, 15 union all
select 2, 18 union all
select 3, 17 union all
select 4, 15 union all
select 5, 15

select SalesID from @t t1 join
(
select CustAccount from @t group by custAccount having count(SalesID) > 1
) t2
on t1.CustAccount = t2.CustAccount[/code]

Harsh Athalye
http://www.letsgeek.net/

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-01 : 08:42:18

Can it be done without declaring another table?

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-01 : 08:46:31

SELECT S1.SALESID FROM SALESTABLE S1 JOIN
(SELECT SALESTABLE.CUSTACCOUNT FROM SALESTABLE GROUP BY CUSTACCOUNT
HAVING COUNT(SALESTABLE.CUSTACCOUNT) > 1) S2 ON S1.CUSTACCOUNT = S2.CUSTACCOUNT

Got it, thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 13:20:25

quote:
Originally posted by SQLSoaker

Can it be done without declaring another table?

the declare table was just for illustration. solution is only last select. you need to just replace the @t table with your actual field and also the fields.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-01 : 13:30:10

quote:
Originally posted by visakh16

quote:
Originally posted by SQLSoaker

Can it be done without declaring another table?

the declare table was just for illustration. solution is only last select. you need to just replace the @t table with your actual field and also the fields.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I think that is what he has done

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 13:34:12

quote:
Originally posted by webfred

quote:
Originally posted by visakh16

quote:
Originally posted by SQLSoaker

Can it be done without declaring another table?

the declare table was just for illustration. solution is only last select. you need to just replace the @t table with your actual field and also the fields.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I think that is what he has done

No, you're never too old to Yak'n'Roll if you're too young to die.

I missed the last response. Was looking at the prev response

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources