Going beyond a GROUP BY Clause

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 2000 Forums

SQL Server Development (2000)

Going beyond a GROUP BY Clause

Author

Topic

edb2003
Yak Posting Veteran

66 Posts

Posted - 2004-11-08 : 18:21:20

Greetings,
I have a database that I need to define the return list better than using a GROUP BY. Each month an upload is added to the database. I need to check for duplicate AccountNumbers and Product Descriptions along with DateEntered.

Example
AccountNumber ProductDescription UnitPrice InvoiceDate.
12345 Can of Tuna $10.00 4/1/2004
12345 Can of Tuna $24.00 6/1/2004

I had this script as a view in SQL but it grabs dupes in any form or array.

SELECT TOP 100 PERCENT AccountNumber, ProductDescription, UnitPrice, InvoiceDate
FROM dbo.Invoices
GROUP BY AccountNumber, ProductDescription, UnitPrice, InvoiceDate
HAVING (COUNT(*) > 1)
ORDER BY AccountNumber DESC

Can someone help me right a better script to simply define just the AccountNumber and ProductDescription and not just each field for dupes?

I appreciate everyones help :)

Happy Coding
Edb

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-08 : 18:24:25

[code]

SELECT *
FROM dbo.Invoices i
INNER JOIN
(
SELECT AccountNumber, ProductDescription, COUNT(*)
FROM dbo.Invoices
GROUP BY AccountNumber, ProductDescription
HAVING (COUNT(*) > 1)
) t
ON i.AccountNumber = t.AccountNumber and i.ProductDescription = t.ProductDescription

[/code]

Is that what you are looking for?

Tara

edb2003
Yak Posting Veteran

66 Posts

Posted - 2004-11-08 : 18:38:10

I was trying to run this but got an error ...

No column was specified for column 3 of t

It looks like the COUNT(*) is not being identified.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-08 : 18:39:01

[code]

SELECT *
FROM dbo.Invoices i
INNER JOIN
(
SELECT AccountNumber, ProductDescription, COUNT(*) AS DupCount
FROM dbo.Invoices
GROUP BY AccountNumber, ProductDescription
HAVING (COUNT(*) > 1)
) t
ON i.AccountNumber = t.AccountNumber and i.ProductDescription = t.ProductDescription

[/code]

Tara

edb2003
Yak Posting Veteran

66 Posts

Posted - 2004-11-08 : 18:49:19

This is perfect

- Thanks Tara. What books do you use to come up with scripts like these? You are very helpful. There should be a tips area. I would make a contribution.

Happy Day,
Edb

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-08 : 21:54:11

Books Online is a good place to start, there are a bunch of SQL examples there. We also have a script library forum:

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=11

By all means post your good stuff in there!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources