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.
Author |
Topic |
aademeo
Starting Member
5 Posts |
Posted - 2014-08-04 : 20:36:26
|
I'm trying to Display 2 fields using the SalesOrderDetail table. CarrierTrackingNumberCount the number of CarrierTrackingNumber and give alias name of “Count CarrierTrackingNumber.” I only want to return records where the CarrierTrackingNumber has count = or greater than 2.Below is the SQL I developed, but I'm getting this error when I try to execute: Msg 245, Level 16, State 1, Line 1Conversion failed when converting the nvarchar value '4911-403C-98' to data type int.SELECT CarrierTrackingNumber, ( SELECT COUNT(CarrierTrackingNumber) FROM SalesOrderDetail WHERE CarrierTrackingNumber >= 2 ) AS CountCarrierTrackingNumber FROM SalesOrderDetail |
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-08-04 : 20:56:19
|
You're probably looking for:select CarrierTrackingNumber ,count(*) as CountCarrierTrackingNumber from SalesOrderDetail group by CarrierTrackingNumber having count(*)>=2 |
|
|
aademeo
Starting Member
5 Posts |
Posted - 2014-08-05 : 20:45:33
|
Thank you!! I'm still trying to understand subqueries and table joins and how to know when to use or not. For example, I think the below requires a Subquery and a join, but not sure what. Thinking it's a join only because there's a ProductID attribute in both the Product table and the SalesOrderDetail table. After that, I'm not really sure. Display Product Name from Product table. In the SELECT statment write subquery to count the number of SalesOrderDetail associated with each Product ID from the SalesOderDetailtable. Call new alias column SalesOrderDetailCount. Order By Product ID I put together the below query, which brings results, but the SalesOrderDetailCount is the same value for each row, which I'm not sure is correct or not. SELECT Name, ( SELECT COUNT(*) FROM SalesOrderDetail ) AS SalesOrderDetailCountFROM ProductORDER BY ProductID |
|
|
aademeo
Starting Member
5 Posts |
Posted - 2014-08-05 : 21:31:11
|
Would the below work?Select p.Name, sod.SalesOrderDetailFROM Product p INNER JOIN ( SELECT ProductID, COUNT(SalesOrderDetailID) AS SalesOrderDetail FROM SalesOrderDetail GROUP BY ProductID ) AS sod ON p.ProductID = sod.ProductIDORDER BY p.ProductID |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-08-05 : 21:45:45
|
You are pretty close on your first try - only thing missing is associating the product id - something like:select name ,(select count(*) from salesorderdetail where salesorderdetail.productid=product.productid ) as salesorderdetailcount from product order by productid Second try would work, all though if a product hasn't been sold, it won't be shown. This can be "rectified" changing the "inner join" to "left outer join". Something likeselect p.name ,isnull(sod.sodcount,0) as salesorderdetailcount from product as p left outer join (select productid ,count(*) as sodcount from salesorderdetail group by productid ) as sod on p.productid=sod.productid order by p.productid |
|
|
|
|
|
|
|