Wednesday, 28 August 2013

SQL Count in Where clause

SQL Count in Where clause

I'm trying to find items that have shown up one time or less on a report.
I know that to find how many times each item has apepared, I use this.
select COUNT(VP.VendorPartID)
from Purchasing.PurchaseOrder PO with (nolock)
inner join dbo.tblVendor V with (nolock)
on PO.VendorID=V.VendorID
inner join Purchasing.PurchaseOrderItem POI with (nolock)
on PO.PurchaseOrderID=POI.PurchaseOrderID
inner join Purchasing.VendorPart VP with (nolock)
on POI.VendorPartID=VP.VendorPartID
where V.ProductTypeID=4
group by PO.PurchaseOrderID
But I tried to nest it within another query to be able to set that it must
appear 1 time or less, and it says there's an error because
"Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
expression."
I did this, which I'm guessing is likely pretty wrong, haha.
select VP.VendorPartID,VP.VendorPartDescription
from Purchasing.PurchaseOrder PO with (nolock)
inner join dbo.tblVendor V with (nolock)
on PO.VendorID=V.VendorID
inner join Purchasing.PurchaseOrderItem POI with (nolock)
on PO.PurchaseOrderID=POI.PurchaseOrderID
inner join Purchasing.VendorPart VP with (nolock)
on POI.VendorPartID=VP.VendorPartID
where (
select COUNT(VP.VendorPartID)
from Purchasing.PurchaseOrder PO with (nolock)
inner join dbo.tblVendor V with (nolock)
on PO.VendorID=V.VendorID
inner join Purchasing.PurchaseOrderItem POI with (nolock)
on PO.PurchaseOrderID=POI.PurchaseOrderID
inner join Purchasing.VendorPart VP with (nolock)
on POI.VendorPartID=VP.VendorPartID
where V.ProductTypeID=4
group by PO.PurchaseOrderID
) < 2
group by VP.VendorPartID,VP.VendorPartDescription

2 comments:

  1. It is not easy for you to find the best places where you can shop for your wedding supplies. It is also very difficult for you to create wedding decorations on your own by using cheap objects and craft supplies that you can turn into pieces that look expensive. biketowork2013 |

    midwestmarketingtesting |

    booksharmexcursions |

    experiencetheserenity |

    jabuconsulting |


    ReplyDelete
  2. Dogs provide lots of love and affection to humans when they get proper care. You must get to know what your dog wants to make sure they are happy.
    http://www.latinosposttech.com |

    http://www.lighthouseautomotivellccos.com |

    http://www.loire-forez-tourism.com |

    http://www.luzmiamihomes.com |

    http://www.mnhousecleaning.com |

    ReplyDelete