How to query for multiple entries in a column

Question

I have a customer database and i'm looking to run a query to find any barcode that was used more than once in a day and output just the barcode... So for example if barcode #1 was scanned 3 times on a specific date it will show 3 transactions in the database all with the barcode #1... Here is a basic query I have now searching by date...

Select *
FROM            Customers
WHERE        (dtCreated BETWEEN CONVERT(DATETIME, '2012-12-10 00:00:00', 102) AND CONVERT(DATETIME, '2012-12-11 00:00:00', 102)) AND (sBarcode = '1')

Instead of searching for barcode 1, i'd like to know ANY barcodes (sBarcode) that was scanned more than once on 12/10/12.

Rohit · Accepted Answer · 2013-01-01 06:35:35Z

1

select barcode, count(barcode) as cnt from table_name where date = desired_date group by barcode HAVING (COUNT(*) > 1)

edited Jan 1, 2013 at 6:35

answered Dec 11, 2012 at 13:29

Rohit

5253 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shmewnix Over a year ago

This is close, however it says invalid column name for 'cnt'

Shmewnix Over a year ago

HAVING (COUNT(*) > 1) and it's correct. If you edit your post I can give you credit for the answer.

phaazon · Accepted Answer · 2012-12-11 13:30:41Z

1

Just remove the last AND statement?

EDIT: ok. You have to group by barcode, agregating on each day. That’s all. Something like:

SELECT *
WHERE (dtCreated BETWEEN CONVERT(DATETIME, '2012-12-10 00:00:00', 102) AND CONVERT(DATETIME, '2012-12-11 00:00:00', 102))
GROUP BY dtID, dtCreated
HAVING COUNT(*) > 1

edited Dec 11, 2012 at 13:30

answered Dec 11, 2012 at 13:25

phaazon

2,01216 silver badges21 bronze badges

2 Comments

Shmewnix Over a year ago

No, that will return ALL customers created on the 10th. I'm looking for duplicate barcode creations.

Shmewnix Over a year ago

I stole the HAVING (COUNT(*) > 1) from yours and used TwTw and Rohit query. Thanks!

TwTw · Accepted Answer · 2012-12-11 13:53:38Z

1

Select Count(barcode) counter
FROM  Customers
WHERE  (dtCreated BETWEEN CONVERT(DATETIME, '2012-12-10 00:00:00', 102) AND CONVERT(DATETIME, '2012-12-11 00:00:00', 102)) AND (sBarcode = '1')
group by barcode
having (COUNT(barcode) >= 1)

edited Dec 11, 2012 at 13:53

answered Dec 11, 2012 at 13:34

TwTw

5474 silver badges16 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2012-12-11 16:27:04Z

0

I would do this:

select sbarcode
from customers
where cast(dtCreated as date) = '2012-12-10'
group by sbarcode
having count(*) > 1

If you wanted the count, then you would include , count(*) on the select line.

answered Dec 11, 2012 at 16:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to query for multiple entries in a column

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related