How to limit data when grouping

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

How to limit data when grouping

Author

Topic

LOLCatLady
Starting Member

24 Posts

Posted - 2012-02-10 : 10:51:01

I have this basic code that returns the number of orders by id within a specific timeframe. I need to limit the return data to all orders with a count of 4 or less. I don't want to see anybody with 5 or more orders. What do I need to add?

SELECT id, COUNT(effdat) AS CountOrdereffdat
FROM orders
WHERE (ordereffdat BETWEEN '03/01/2011' AND '02/29/2012')
AND (ordertype = 'g')
AND (id NOT LIKE 'C%')
GROUP BY id
ORDER BY id

Sample returned data from above code:
ID CountOrdereffdat
00072 6
00093 6
00130 1
02360 3
02896 4
98765 10

Thanks for helping!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-10 : 10:52:25

Use a HAVING clause


SELECT id, COUNT(effdat) AS CountOrdereffdat
FROM orders
WHERE (ordereffdat BETWEEN '03/01/2011' AND '02/29/2012')
AND (ordertype = 'g')
AND (id NOT LIKE 'C%')
GROUP BY	id
HAVING COUNT([effdat]) <= 4
ORDER BY	id

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

LOLCatLady
Starting Member

24 Posts

Posted - 2012-02-10 : 10:54:55

That was quick and it works! Thanks so much!

quote:
Originally posted by Transact Charlie

Use a HAVING clause

SELECT id, COUNT(effdat) AS CountOrdereffdat
FROM orders
WHERE (ordereffdat BETWEEN '03/01/2011' AND '02/29/2012')
AND (ordertype = 'g')
AND (id NOT LIKE 'C%')
GROUP BY id
HAVING COUNT([effdat]) <= 4
ORDER BY id

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources