How to group the total count of similar items? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2008 Forums

Transact-SQL (2008)

How to group the total count of similar items?

Author

Topic

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2015-03-30 : 16:47:05

We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.
So some sample records of a complete package would look like this:

ItemId | ParentId | Name | QtyAvailable
----------------------------------------
1 | NULL | Package A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3

ItemId's 2 & 3 are items contained within the ItemId 1 package.

Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:

Name | Available Qty
--------------------------
Package A | 15
Package B | 100

How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?

Thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-03-30 : 18:11:07

I would argue that the total quantity available is the package quantity plus the minimum of its components. How can you make 5 new "Package A" items when you only have 2 "Item 1"s available to put in? Regardless, this should give you the totals you asked for. You can modify it if the requirements dictate:

declare @Items table (
   ItemId       int not null,
   ParentId     int null,
   Name         varchar(50) not null,
   QtyAvailable int not null
   )

insert into @Items
values 
   (1, null, 'Package A', 10),
   (2, 1, 'Item 1', 2),
   (3, 1, 'Item 3', 3)

-- select * from @Items

;with PkgSums
as   (
select coalesce(ParentId, ItemId) ItemID, sum(QtyAvailable) SumQtyAvailable
from @Items
group by coalesce(ParentId, ItemId)
)
select
   i.Name, s.SumQtyAvailable
from
   @Items i
inner join
   PkgSums s
      on s.ItemID = i.ItemId

-- This might perform better (?)

select
   i.Name, s.SumQtyAvailable
from
   @Items i
cross apply (
   select coalesce(ParentId, ItemId) ItemID, sum(QtyAvailable) SumQtyAvailable
   from @Items i2
   where coalesce(i2.ParentId, i2.ItemId) = i.ItemId
   group by coalesce(ParentId, ItemId)
   ) s
where
   i.ParentId is null

I would rather be the man who bought the Brooklyn Bridge than the one who sold it. -Will Rogers

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-31 : 10:05:43

[code]
SELECT
MAX(CASE WHEN i.ParentId IS NULL THEN '' ELSE i.Name END) AS Name,
SUM(i.QtyAvailable) AS [Available Qty]
FROM @Items i
GROUP BY ISNULL(i.ParentId, i.ItemId)
--ORDER BY Name

[/code]

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2015-03-31 : 10:29:53

Thanks for both of your responses. I was able to implement them & my query is working great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources