Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am currently trying to create a view(SQL) in which i can get the count of products and from the count be able to show any products which the count is greater than 1.

[edit]additional OP's information moved from non-solution[/edit]
I am not sure how to get the count of each product to show in the view

SQL
SELECT     <big>COUNT(dbo.Receiving.ManufacturerSerialNumber) AS Cnt</big>, dbo.Receiving.ManufacturerSerialNumber, dbo.Receiving.UserId, dbo.Receiving.WorkCompleted,
                      dbo.FullRepair.EngineersReportId, dbo.FullRepair.UserId AS Technician
FROM         dbo.Receiving INNER JOIN
                      dbo.FullRepair ON dbo.Receiving.ManufacturerSerialNumber = dbo.FullRepair.ProductSerial
Posted
Updated 1-Nov-12 23:00pm
v2
Comments
Mehdi Gholam 2-Nov-12 3:35am    
... and what is your problem?

Try something like this (check out HAVING):
SELECT COUNT(*) AS Cnt, 
       dbo.Receiving.ManufacturerSerialNumber, 
       dbo.Receiving.UserId, 
       dbo.Receiving.WorkCompleted,  
       dbo.FullRepair.EngineersReportId, 
       dbo.FullRepair.UserId AS Technician
     FROM dbo.Receiving, dbo.FullRepair
     WHERE dbo.Receiving.ManufacturerSerialNumber = dbo.FullRepair.ProductSerial
     HAVING COUNT(*) > 1;

Good luck!
 
Share this answer
 
I have found a solution thanks
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900