Subquery Help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Subquery Help

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.

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
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 SalesOrderDetailCount

FROM

Product

ORDER BY ProductID

aademeo
Starting Member

5 Posts

Posted - 2014-08-05 : 21:31:11

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

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 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources