count instances in a table *addition*

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 instances in a table *addition*

Author

Topic

fenster89411
Starting Member

7 Posts

Posted - 2009-10-24 : 08:23:23

Hey Guys,

This is my first post and could realy do with some advice/help.

I have a table called reciepts.
The table has individulal rows for items bought.
each row holds the item and transaction number.

Example:

Transaction number Item
2234 2
2234 1
2234 3
2234 6
2235 1
2236 8
2236 4
2237 2

etc

I need to know all people who bought 20 items or more in a single transaction.

so the query would be select * from reciepts where the same transaction number => 20

I would like the return to be just the transaction numbers who meet this criteria rather than displaying the full 20 or so rows.

is this easy to achieve? Can anyone get me started on this?

And I hope this makes sense...
Thanks in advance..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-24 : 09:27:28

select TransNo from table group by TransNo having count(*) > 19

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

fenster89411
Starting Member

7 Posts

Posted - 2009-10-24 : 10:31:04

Thanks WebFred works a charm.

fenster89411
Starting Member

7 Posts

Posted - 2009-10-24 : 15:25:42

Can anyone show me an example of adding an inner join using WebFreds query?

The table reciepts also has a client number which can be joined to table clients.clientnumber

when I try I get errors on the Group by part.

Im just learning SQL Query so apologies for the lame questions...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-25 : 11:01:53

select DISTINCT
r.TransNo,
c.what_column_ever
from
reciepts r
join (select TransNo from table group by TransNo having count(*) > 19)dt
on dt.TransNo = r.TransNo
join clients c
on c.Clientnumber = r.Clientnumber

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

fenster89411
Starting Member

7 Posts

Posted - 2009-10-26 : 05:38:46

Again I thank you!