SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Subquery Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aademeo
Starting Member

5 Posts

Posted - 08/04/2014 :  20:36:26  Show Profile  Reply with Quote

I'm trying to Display 2 fields using the SalesOrderDetail table.

CarrierTrackingNumber
Count 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 1
Conversion 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
Constraint Violating Yak Guru

388 Posts

Posted - 08/04/2014 :  20:56:19  Show Profile  Reply with Quote
You're probably looking for:
select CarrierTrackingNumber
      ,count(*) as CountCarrierTrackingNumber
  from SalesOrderDetail
 group by CarrierTrackingNumber
 having count(*)>=2
Go to Top of Page

aademeo
Starting Member

5 Posts

Posted - 08/05/2014 :  20:45:33  Show Profile  Reply with Quote
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 SalesOrderDetailCount

FROM

Product

ORDER BY ProductID
Go to Top of Page

aademeo
Starting Member

5 Posts

Posted - 08/05/2014 :  21:31:11  Show Profile  Reply with Quote
Would the below work?

Select

p.Name,

sod.SalesOrderDetail
FROM
Product p INNER JOIN

(
SELECT ProductID, COUNT(SalesOrderDetailID) AS SalesOrderDetail

FROM SalesOrderDetail

GROUP BY ProductID

) AS sod

ON p.ProductID = sod.ProductID

ORDER BY p.ProductID
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

388 Posts

Posted - 08/05/2014 :  21:45:45  Show Profile  Reply with Quote
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 like
select 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000