SQL Query to obtain rows that have >1 purchases

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)

SQL Query to obtain rows that have >1 purchases

Author

Topic

kiki_1410
Starting Member

1 Post

Posted - 2007-09-10 : 03:08:26

Hi,

I am new at SQL hopefully this would be a rather easy question for you guys to help me out with.

I have a table called PRODUCT with the following fields:
a. Product Name
b. Product Dept.
c. Purchase Date.

I would like to run a query to obtain all rows that has more than one purchases on any particular day.

Any replies would be great.

Kristen
Test

22859 Posts

Posted - 2007-09-10 : 03:45:53

SELECT [Product Name], [Purchase Date]
FROM PRODUCT
GROUP BY [Product Name], [Purchase Date]
HAVING COUNT(*) > 1
ORDER BY [Product Name]

If [Purchase Date] include BOTH the Date AND Time then you will need to replace [Purchase Date] with:

DATEADD(Day, 0, DATEDIFF(Day, 0, [Purchase Date]))

(which will efficiently remove the Time part)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources