select count where count is higher than specified

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

SQL Server 2005 Forums

Transact-SQL (2005)

select count where count is higher than specified

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-08-10 : 10:58:30

want this select to work just like it is.. but only display the groups that have more that specified # of records... > 2 or whatever

thanks

SELECT     COUNT(a.ITEMDESC) AS Times_Billed, a.ITEMDESC, c.CUSTNAME
FROM         dbo.BOA_SOP_Lines_vw AS a INNER JOIN
                      dbo.BOA_SOP_HEADER_vw AS c ON a.SOPNUMBE = c.SOPNUMBE
WHERE     (c.CUSTNMBR IN ('1', '2', '3', '4', '5', '6', '7', '8'))
GROUP BY a.ITEMDESC, c.CUSTNAME
ORDER BY c.CUSTNAME

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-10 : 11:11:17

add a HAVING clause:

where...
group by ...
HAVING count(*) > 2
order by ...

Be One with the Optimizer
TG

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-08-10 : 11:21:55

Great thanks!

quote:
Originally posted by TG

add a HAVING clause:

where...
group by ...
HAVING count(*) > 2
order by ...

Be One with the Optimizer
TG

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 11:45:00

or try like this
select * from
(SELECT COUNT(a.ITEMDESC) AS Times_Billed, a.ITEMDESC, c.CUSTNAME
FROM dbo.BOA_SOP_Lines_vw AS a INNER JOIN
dbo.BOA_SOP_HEADER_vw AS c ON a.SOPNUMBE = c.SOPNUMBE
WHERE (c.CUSTNMBR IN ('1', '2', '3', '4', '5', '6', '7', '8'))
GROUP BY a.ITEMDESC, c.CUSTNAME
)where Times_Billed > 2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources