CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
Robert
Robert

Reputation: 1726

SQL - group rows on criteria and show total

Not too sure how to put a title on this but I did the best I could. I have a report which displays all the assets for a selected location. One of our problems is that we track EVERYTHING as an asset. So a typical computer system would have 5 computers, 8 monitors, but 10+ network cables. I'm trying to write a report that would "roll-up" any of the unnecessary items and show a count instead of the actual item.

For instance, I'd like this: 

Desc            | Manufacturer  | Serial Number
-----+-------    ----+--------------------
20" monitor     | Dell          | 123456
25" monitor     | Dell          | 123456ab
6ft Net. Cable  | N/A           | NA123
6ft Net. Cable  | N/A           | NA124
6ft Net. Cable  | N/A           | NA125
6ft Net. Cable  | N/A           | NA456
1TB SATA HD     | SeaGate       | SG125000
1.5TB SATA HD   | SeaGate       | SG100000

To be:

Desc            | Manufacturer  | Serial Number/Qnty
-----+-------    ----+-----------------------------
20" monitor     | Dell          | 123456
25" monitor     | Dell          | 123456ab
6ft Net. Cable  | N/A           | 4
1TB SATA HD     | SeaGate       | SG125000
1.5TB SATA HD   | SeaGate       | SG100000

Here's my SQL that gets me the top portion but I'm not too sure where to even start to get the bottom results.

SELECT i.ItemDescription AS Desc, s.Name AS Manufacturer, inv.SerialNumber AS [Serial Number]
FROM Assets a 
LEFT OUTER JOIN Inventory inv ON a.InventoryID = inv.InventoryID 
LEFT OUTER JOIN Items i ON inv.ItemID = i.ItemID 
LEFT OUTER JOIN Rooms r ON a.RoomID = r.RoomID 
LEFT OUTER JOIN Locations l ON r.LocationID = l.LocationID 
LEFT OUTER JOIN Suppliers s ON i.ManufacturerID = s.SupplierID 
WHERE l.LocationID = 5

I need to have the "grouped" row be based on criteria, so it would have to be in the WHERE clause, something like: WHERE l.LocationID = 5 AND inv.SerialNumber LIKE 'NA%' but I'm not too sure where to put that so it doesn't filter all my records to just those with a serial number like 'NA%'

Any help is greatly appreciated!

Upvotes: 1

Views: 224

Answers (2)

Taryn
Taryn

Reputation: 247650

You should be able to use the following:

;with cte as
(
  SELECT i.ItemDescription AS Desc, s.Name AS Manufacturer, inv.SerialNumber AS [Serial Number]
  FROM Assets a 
  LEFT OUTER JOIN Inventory inv ON a.InventoryID = inv.InventoryID 
  LEFT OUTER JOIN Items i ON inv.ItemID = i.ItemID 
  LEFT OUTER JOIN Rooms r ON a.RoomID = r.RoomID 
  LEFT OUTER JOIN Locations l ON r.LocationID = l.LocationID 
  LEFT OUTER JOIN Suppliers s ON i.ManufacturerID = s.SupplierID 
  WHERE l.LocationID = 5
),
totals as
(
  select [Desc], count(*) TotalCount
  from cte
  group by [desc]
)
select c.[desc],
  c.Manufacturer,
  case 
    when t.TotalCount > 1 
    then cast(t.TotalCount as varchar(50))
    else [Serial Number]
  end [Serial Number/Qnty]
from cte c
inner join totals t
  on c.[desc] = t.[desc]

Upvotes: 1

rs.
rs.

Reputation: 27427

Try this 

;with cte as

(
    SELECT i.ItemDescription AS Desc, s.Name AS Manufacturer, 
    CASE WHEN isnull(inv.SerialNumber,'') like 'NA%' Then 'NA'
         Else inv.SerialNumber end AS [Serial Number]

     FROM Assets a 
     LEFT OUTER JOIN Inventory inv ON a.InventoryID = inv.InventoryID 
     LEFT OUTER JOIN Items i ON inv.ItemID = i.ItemID 
     LEFT OUTER JOIN Rooms r ON a.RoomID = r.RoomID 
     LEFT OUTER JOIN Locations l ON r.LocationID = l.LocationID 
     LEFT OUTER JOIN Suppliers s ON i.ManufacturerID = s.SupplierID 
     WHERE l.LocationID = 5

), cte2 as
(
     SELECT Desc, Manufacturer, [Serial Number], Count(*) cnt
     FROM cte2
     GROUP BY Desc, Manufacturer, [Serial Number]
)
SELECT Desc, Manufacturer, CASE WHEN isnull([Serial Number],'') = 'NA' 
                                THEN CAST(cnt as varchar)
                                else [Serial Number] end [Serial Number]
from cte2;

Upvotes: 0

Related Questions