How to get the line item count from an order

• josetur12

  Default port

  Points: 1424

  More actions

  May 28, 2014 at 2:33 pm

  #100556

  Hi this is my DDL

  DROP TABLE [dbo].[Order]

  GO

  CREATE TABLE [dbo].[Order](

  [OrderID] [int] NOT NULL)

  Insert into [dbo].[Order]

  (OrderID)

  SELECT 100 UNION ALL

  SELECT 101 UNION ALL

  SELECT 102

  DROP TABLE [dbo].[OrderDetail]

  GO

  CREATE TABLE [dbo].[OrderDetail](

  [OrderID] [int] NOT NULL,

  [ItemID] [int] NOT NULL,

  [Status] [char](1) NULL,)

  Insert into [dbo].[OrderDetail]

  (OrderID, ItemID, Status)

  SELECT 100, 1, 'R' UNION ALL

  SELECT 100, 2, 'R' UNION ALL

  SELECT 100, 3, 'P' UNION ALL

  SELECT 101, 1, 'P' UNION ALL

  SELECT 101, 2, 'P' UNION ALL

  SELECT 102, 1, 'R' UNION ALL

  SELECT 102, 2, 'R'

  based on the sample data, I need to retrieve the output below.

  OrderIdItemCount

  1002

  1022

  I need to join both tables and get the order id from the order table and the item count from the orderdetail table where the status = 'F'

  I will appreciate any help.

  Thanks,

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  May 28, 2014 at 2:47 pm

  #1717169

  josetur12 (5/28/2014)

  ---

  Hi this is my DDL

  DROP TABLE [dbo].[Order]

  GO

  CREATE TABLE [dbo].[Order](

  [OrderID] [int] NOT NULL)

  Insert into [dbo].[Order]

  (OrderID)

  SELECT 100 UNION ALL

  SELECT 101 UNION ALL

  SELECT 102

  DROP TABLE [dbo].[OrderDetail]

  GO

  CREATE TABLE [dbo].[OrderDetail](

  [OrderID] [int] NOT NULL,

  [ItemID] [int] NOT NULL,

  [Status] [char](1) NULL,)

  Insert into [dbo].[OrderDetail]

  (OrderID, ItemID, Status)

  SELECT 100, 1, 'R' UNION ALL

  SELECT 100, 2, 'R' UNION ALL

  SELECT 100, 3, 'P' UNION ALL

  SELECT 101, 1, 'P' UNION ALL

  SELECT 101, 2, 'P' UNION ALL

  SELECT 102, 1, 'R' UNION ALL

  SELECT 102, 2, 'R'

  based on the sample data, I need to retrieve the output below.

  OrderIdItemCount

  1002

  1022

  I need to join both tables and get the order id from the order table and the item count from the orderdetail table where the status = 'F'

  I will appreciate any help.

  Thanks,

  Do you mean you want the count of LineItems where the Status = 'R', not 'F'?

  What have you tried? This looks a lot like homework. You will learn a lot more if you try to figure this out on your own. Hint, COUNT.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• josetur12

  Default port

  Points: 1424

  More actions

  May 28, 2014 at 2:50 pm

  #1717171

  Thank you, I think I just got it...